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