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:

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


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 =' <some_column> EQUALs <some_regex>

REM one text-criterion
Dim fld as new
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 =
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:
End Sub

REM one of my frequently used helper functions
Function getUsedRange(oSheet)
Dim oRg
   oRg = oSheet.createCursor()
   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
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