Hi! How do I create a macro to delete an entire row based off of a string within the cell?

[mxc5425]

I’m working on some data cleaning in a calc spreadsheet. In Column F I have multiple rows that say “I’m a number”. When this occurs I want to delete the entire row. Otherwise I want to leave the rows untouched.

I’m at a loss about how to even start this.

Any help/advice is appreciated!

[JohnSUN]

The sequence of actions that the script must perform is not complicated, it fully corresponds to the steps that you described in your question:

1. Select one column from the whole sheet (in your case, this is column F, but it can be any)

2. Find a line of text in the selected column (in your case, this is the text “I’m a number”, but it can also be any)

3. If the text is found, then delete the rows with the cells where it was found.

In StarBacic it can be written like this :

Sub deleteRows
 delRowsByTextInColumn(ThisComponent.getCurrentController().getActiveSheet(), _
      5, "I'm a number")
End Sub

The implementation of the procedure delRowsByTextInColumn() can be of any complexity. For example, the simplest option (without checking the correctness of the input parameters, without handling error situations, is not the most optimal in terms of speed):

Sub delRowsByTextInColumn(oSheet As Variant, nColumn As Long, sText As String)
Dim oColumn As Variant	' Entire column for search '
Dim oSearchDescriptor As Variant	
Dim oFound As Variant	' Result of search '
Dim oCells As Variant	' One Part of oFound (subrange) '
Dim oRows As Variant	' All rows of oCells '
Dim i As Long 
	oColumn = oSheet.getColumns().getByIndex(nColumn)	' 1. One column from the whole sheet '
	oSearchDescriptor = oColumn.createSearchDescriptor()
	oSearchDescriptor.setSearchString(sText)	' 2a. Set a text to search '
	oFound = oColumn.findAll(oSearchDescriptor)	' 2b. Find this text in the selected column '
	If Not IsNull(oFound) Then 					' 3a. If the text is found... '
		For i = oFound.getCount() - 1 To 0 Step -1	' then for each part of search result '
			oCells = oFound.getByIndex(i)		' in backward order '
			oRows = oCells.getRows()			' 3b. all rows of subrange '
			oRows.removeByIndex(0, oRows.getCount())	' 3c. remove '
		Next i
	EndIf 
End Sub

However, it should be noted that the use of the available built-in Calc tools is enough to complete your task. Using the Standard Filter allows you to perform the action described by you without writing any macros.

[mxc5425]

Thanks for your help! I’m trying to automate as much as I can. I’ve noticed with the code above is erring on this string:

oColumn = oSheet.getColumns().getByIndex(nColumn)

I’m receiving the error message “Basic Runtime Error. Argument is not optional” Is this error because there is currently nothing in the parentheses for getColumns?

[JohnSUN]

There is no error in the code; you make an error when you try to execute the delRowsByTextInColumn procedure directly. To delete from the current (active) sheet all rows that contain the text “I’m a number” in the F column, you must run the deleteRows procedure