Ask Your Question
0

Unfiltering in a macro

asked 2019-05-14 16:49:53 +0200

david31650 gravatar image

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
edit retag flag offensive close merge delete

Comments

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

newbie-02 gravatar imagenewbie-02 ( 2019-05-14 18:57:46 +0200 )edit

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 :-(

david31650 gravatar imagedavid31650 ( 2019-05-14 19:57:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-19 10:31:41 +0200

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-14 16:49:53 +0200

Seen: 25 times

Last updated: May 19