Ask Your Question
0

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

asked 2018-12-29 04:50:15 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-12-29 10:16:44 +0100

JohnSUN gravatar image

updated 2018-12-29 10:18:28 +0100

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.

delRowsByTextInColumn.png

edit flag offensive delete link more

Comments

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?

mxc5425 gravatar imagemxc5425 ( 2018-12-30 02:39:29 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2018-12-30 07:44:50 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-29 04:50:15 +0100

Seen: 31 times

Last updated: Dec 29 '18