If I needed to filter by a text criterion this would be perfect, I can see how to change the column and text but how do I change the column to 'none' and remove/disable the text criterion?
EDIT: Figured it out! Had to remove this bit:
fd.setFilterFields(Array(fld))
It would still be nice if I understood this stuff so I could output to a cell on another sheet though:
REM if true, an OutputPosition as c.s.s.table.CellAddress is required
fd.CopyOutputData = False
viewtopic.php?f=20&t=17037#p78786
Code: Select all
Sub applyMyCustomFilter
REM Filter one column of the active sheet's used range by 1 regular expression
'calls: getUsedRange
REM Which column index of the used range? (first column has index 0):
Const colID = 6 '< column #6 of the used range
REM if the used range starts in column A (column #0), colID 6 refers to column G
Const sRegex = "^(300)|(301)|(302)$" '< this is one regex including the ORs as |
cEQ = com.sun.star.sheet.FilterOperator.EQUAL' <some_column> EQUALs <some_regex>
REM one text-criterion
Dim fld as new com.sun.star.sheet.TableFilterField
fld.Field = colID
fld.Operator = cEQ
fld.StringValue = sRegex
REM now for the used range to be filtered:
sh = ThisComponent.CurrentController.getActiveSheet()
rg = getUsedRange(sh)
fd = rg.createFilterDescriptor(True)
REM All the additional filter options. Fill out like a form (True or False)
REM analogue to those in the standard and advanced filter dialogues below [More Options]:
REM Do we have column headers in the first row?
fd.ContainsHeader = True
REM Do we use pattern matching by means of regular expressions?
fd.UseRegularExpressions = True
REM not relevant with text values consisting of digits:
fd.IsCaseSensitive = False
REM means: hide duplicates having the same values in the entire row of the filtered range:
fd.SkipDuplicates = False
REM if true, an OutputPosition as c.s.s.table.CellAddress is required
fd.CopyOutputData = False
REM This is only implemented for rows at the moment. Filtering columns is not possible:
REM fd.Orientation = com.sun.star.table.TableOrientation.ROWS
REM Only relevant with CopyOutputData when refreshing db-ranges:
REM False is useful for one-time snapshots copied to OutputPosition
REM fd.SaveOutputPosition = False
REM we use only one of up to 8 criteria:
fd.setFilterFields(Array(fld))
rg.filter(fd)
End Sub
REM one of my frequently used helper functions
Function getUsedRange(oSheet)
Dim oRg
oRg = oSheet.createCursor()
oRg.gotoStartOfUsedArea(False)
oRg.gotoEndOfUsedArea(True)
getUsedRange = oRg
End Function