Unfiltering in a macro

[david31650]

Hi,

I’m very much a beginner with Calc macros and I am having trouble with filtering ranges.

From information gleaned from the web, I have managed to define a subroutine FILTER to hide significant lines in a data range before exporting (or printing) the range. So far, so good.

After exportation, however, I would like to UNFILTER the data range to:
a) show all the lines again
b) be able to use the classic AUTOFILTER functionality on that range

This I have managed (by guesswork more than knowledge) to achieve (a) above, but not (b).
Once the range has been filtered and unfiltered, the classic AUTOFILTER functionality does not work as expected : when I try to AUTOFILTER to show (for example) only lines with “AAA” or “BBB” in some field, I only get the lines with “AAA”.

I presume that my UNFILTER code is inadequate to achieve (b). Can anybody help?

Here is my code for the routines FILTER and UNFILTER :

Sub Filter()
Dim oSheet       ' Sheet that will contain the filter.
  Dim oRange       ' Range to be filtered.
  Dim oFilterDesc  ' Filter descriptor.
  Dim oFields(0) As New com.sun.star.sheet.TableFilterField
 
  oSheet = ThisComponent.getSheets().getByName("Ordre de passage")
  oFilterDesc = oSheet.createFilterDescriptor(True)
  oRange = oSheet.getCellRangeByName("Ordre_De_Passage_BD")
 
  With oFields(0)
   .Field = 2
    .IsNumeric = False
    .StringValue = "-"
   End With
 
  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
  oRange.filter(oFilterDesc)


End Sub

REM =====================================================================
REM 

Sub Unfilter

  Dim oSheet       ' Sheet that contains range to be unfiltered.
  Dim oRange       ' Range to be unfiltered.
  Dim oFilterDesc  ' Filter descriptor.
 
  oSheet = ThisComponent.getSheets().getByName("Ordre de passage")
  oFilterDesc = oSheet.createFilterDescriptor(True)
  oRange = oSheet.getCellRangeByName("Ordre_De_Passage_BD")

  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
  oRange.filter(oFilterDesc)
  
End Sub

[newbie-02]

hi,

i had some difficulties with filtering and autofilter too, perhaps you can find something about it by googeling for ‘datasurfer’, and after seeing that some of theese disappeared after switch off and on again i created a little makro and assigned it to a keyboard shortcut.

feel free to try if it’s of any help for you.

sub toggle_autofilter

dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
‘’ dim args1(0) as new com.sun.star.beans.PropertyValue
‘’ args1(0).Name = “ToPoint”
‘’ args1(0).Value = “$A$1”
‘’ dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())
dispatcher.executeDispatch(document, “.uno:DataFilterAutoFilter”, “”, 0, Array())

end sub 'toggle_autofilter

[david31650]

Hi newbie02

Thanks for your reply, but your code does exactly the same thing as manually turning off and on the auto filter via menu items. I still get abnormal function of the auto filter after having executed my Filter/Unfilter routines. The only way I have found to restore normal operation of the autofilter is to close the worksheet and then re-open it. Not nice

[SM_Riga]

Hello @david31650

When you apply filter descriptor without copying filtered range to the another position in the spreadsheet (CopyOutputData property is set to False), the filtering is performed by hiding range’s rows, that do not meet filtering conditions. So, to “unfilter” range you need to make these rows visible again:

Sub Unfilter

  Dim oSheet       ' Sheet that contains range to be unfiltered.
  Dim oRange       ' Range to be unfiltered.

  oSheet = ThisComponent.getSheets().getByName("Ordre de passage")
  oRange = oSheet.getCellRangeByName("Ordre_De_Passage_BD")
  oRange.Rows.IsVisible = True

End Sub