You’re absolutely right, this is one of the easiest and fastest ways to organize data entry into your spreadsheet with on-the-fly data filtering. For example, this code will do exactly this task:
Option Explicit
Rem To avoid calling a procedure again until the macro has finished running:
Dim bAlreadyInProgress As Boolean
Const LEAVE_THIS_VALUE = "RRR" ' Keyword that must be in the row
Sub DeleteWithStandardFilterWithTempSheet(oEvent As Variant)
Rem The procedure will copy the source data to a temporary sheet and
Rem apply a standard filter to it, copying the result to the original location
Dim oSheets As Variant, oSheet As Variant, oTempSheet As Variant, sTempSheet As String
Dim oSourceRange As Variant, oFilteredRange As Variant, oCursor As Variant
Dim nEndRow As Long
Dim FilterDescriptor As Variant
Dim oFilterFields2(1) As New com.sun.star.sheet.TableFilterField2
If bAlreadyInProgress Then Exit Sub
bAlreadyInProgress = True
oSheet = oEvent.getSpreadsheet()
oCursor = oSheet.CreateCursor()
oCursor.gotoEndOfUsedArea(False)
nEndRow = oCursor.getRangeAddress().EndRow
oSourceRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
If Not IsNull(oEvent.queryIntersection(oSourceRange.getRangeAddress())) Then
Rem Let's create a temporary auxiliary sheet and copy all the data that should be filtered into it
oSheets = ThisComponent.getSheets()
sTempSheet = "_Temp_" & Format(GetSystemTicks(),"000")
If oSheets.hasByName(sTempSheet) Then oSheets.removeByName(sTempSheet)
oSheets.insertNewByName(sTempSheet, oSheets.getCount())
oTempSheet = oSheets.getByName(sTempSheet)
Rem oSheet.moveRange() should not be used here, otherwise all formulas that refer to these cells will break
oSheet.copyRange(oTempSheet.getCellByPosition(0, 0).getCellAddress(), oSourceRange.getRangeAddress())
Rem A copy of the data has been created, and the original range can be cleared to make place for the filter result
oSourceRange.clearContents(7)
oFilteredRange = oTempSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
Rem The following is a description of the filter.
Rem In fact, many lines are not needed for this task, they have the desired values by default.
Rem I intentionally list all of them, in case anyone wants to see
Rem what is affected by changing these properties of the descriptor.
FilterDescriptor = oFilteredRange.createFilterDescriptor(True)
FilterDescriptor.ContainsHeader = True
FilterDescriptor.CopyOutputData = True
FilterDescriptor.IsCaseSensitive = False
FilterDescriptor.Orientation = com.sun.star.table.TableOrientation.COLUMNS
FilterDescriptor.OutputPosition = oSourceRange.getCellByPosition(0,0).getCellAddress()
FilterDescriptor.SaveOutputPosition = False
FilterDescriptor.SkipDuplicates = False
FilterDescriptor.UseRegularExpressions = False
oFilterFields2(0).Connection = com.sun.star.sheet.FilterConnection.AND
oFilterFields2(0).Field = 0
oFilterFields2(0).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
oFilterFields2(0).IsNumeric = False
oFilterFields2(0).NumericValue = 0.0
oFilterFields2(0).StringValue = LEAVE_THIS_VALUE
oFilterFields2(1).Connection = com.sun.star.sheet.FilterConnection.OR
oFilterFields2(1).Field = 1
oFilterFields2(1).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
oFilterFields2(1).IsNumeric = False
oFilterFields2(1).NumericValue = 0.0
oFilterFields2(1).StringValue = LEAVE_THIS_VALUE
FilterDescriptor.setFilterFields2(oFilterFields2)
oFilteredRange.filter(FilterDescriptor)
Rem It remains only to delete the temporary sheet
oSheets.removeByName(sTempSheet)
EndIf
bAlreadyInProgress = False
End Sub
To be fair, for not very large datasets, the code from the topic you linked to in your question works just as well with a few additions:
Sub DeleteWithLoopInBasic(oEvent As Variant)
Const LAST_SHEET_ROW = 1048575 ' Or use oSheet.getRows().getCount()-1
Dim oSheet As Variant
Dim oCursor As Variant
Dim oQuery As Variant
Dim nEndRow As Long
Dim oCellRange As Variant
Dim oData As Variant
Dim i As Long, k As Long
If bAlreadyInProgress Then Exit Sub
bAlreadyInProgress = True
Rem The range for filtering can be significantly smaller than the used area of the sheet.
Rem We will try to determine its dimensions as accurately as possible.
oSheet = oEvent.getSpreadsheet()
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
nEndRow = oCursor.getRangeAddress().EndRow ' Last used row
Rem Rarely, but it can be: there is something in the very bottom row of the sheet - this can cause an error
If nEndRow < LAST_SHEET_ROW Then
oCellRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow+1)
oQuery = oCellRange.queryEmptyCells()
Rem First empty row after range for filtering:
nEndRow = oQuery.getByIndex(oQuery.getCount()-1).getRangeAddress().StartRow
EndIf
oCellRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
Rem The filtering process is very simple: move the desired rows up
If Not IsNull(oEvent.queryIntersection(oCellRange.getRangeAddress())) Then
oData = oCellRange.getDataArray()
k = LBound(oData)
For i = LBound(oData)+1 To UBound(oData)
If (UCase(oData(i)(0)) = UCase(LEAVE_THIS_VALUE)) Or (UCase(oData(i)(1)) = UCase(LEAVE_THIS_VALUE)) Then
Rem ...or If (oData(i)(0) = LEAVE_THIS_VALUE) Or (oData(i)(1) = LEAVE_THIS_VALUE) Then
k = k + 1
oData(k) = oData(i)
EndIf
Next i
Rem The rest of the array needs to be cleared
If k < UBound(oData) Then
ReDim Preserve oData(k)
oCellRange.clearContents(7)
oSheet.getCellRangeByPosition(0, 0, 1, k).setDataArray(oData)
EndIf
EndIf
bAlreadyInProgress = False
End Sub
You still haven’t answered my question “How many rows are in your table?”. So I can’t really make a recommendation - you should use this algorithm or this one. You will have to make this choice yourself. Do a few experiments with each of the sheets in this spreadsheet FilterInputData.ods (12.7 KB), and decide for yourself.
Pay attention to the line at the very beginning of the code
Const LEAVE_THIS_VALUE = "RRR" ' Keyword that must be in the row
In the process of discussing your problem, you used this placeholder - RRR. That’s why I included it in the macro code. Of course, you will have to replace it with a word that is actually used in your table.
In case you don’t know how to make the macro run at the right moment: just assign the desired code to the sheet event:
