Remove Blank Cells From Column

Creating a macro - Writing a Script - Using the API

Remove Blank Cells From Column

Postby zander73 » Fri Jun 26, 2020 2:05 am

I'd really like to use this filter macro actually, it seems pretty straightforward but I'm having difficulty figuring out how to remove the column/text criterion entirely and ONLY remove duplicates.
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   Expand viewCollapse view
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
Last edited by RusselB on Fri Jun 26, 2020 3:56 am, edited 1 time in total.
Reason: Topic split as the current request now involves a macro
Apache Open Office 4.1.7
Windows 7
zander73
 
Posts: 7
Joined: Thu Jun 04, 2020 3:17 am

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 1 guest