Delete Row Based On A Substring In Excel

In this post we will see how to delete a row in excel if it contains a substring, these type operations are called wildcard search where we search for a particular string in a cell in a column and if the string is found the column ten entire row is deleted with the help of VBA code.

This is the date that we have in Sheet1 in column A and we want to delete entire row if the string “Deletethisrow” is found in column A, we will only delete the row when the entire substring is available in the cell and not the partial substring.

Excel showing rows to be deleted based on certain string

Step 1

We will create a macro in excel to delete entire row if a substring is found in a cell.

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to delete rows based on substring

Step 2

Insert a module in which we will write the program.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Sub Sample()

    Dim ws As Worksheet

    Dim strSearch As String

    Dim lRow As Long

    strSearch = “Deletethisrow”

    Set ws = Sheets(“Sheet1”)

    With ws

        lRow = .Range(“A” & .Rows.Count).End(xlUp).Row

        ‘~~> Remove any filters

        .AutoFilterMode = False

        ‘~~> Filter, offset(to exclude headers) and delete visible rows

        With .Range(“A1:A” & lRow)

          .AutoFilter Field:=1, Criteria1:=”=*” & strSearch & “*”

          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

        End With

        ‘~~> Remove any filters

        .AutoFilterMode = False

   End With

End Sub

This program will loom for the substring “Deletethisrow” and if found will delete the entire row.

VBA code to delete rows based on substring

Step  4

Run the program by pressing F5 or by play button.

Step 5

You can see that after the program is run, we row number 3,5 and 7 were deleted as they had substring “Deletethisrow” within them and note that row number 6 has not been deleted as it just had substring “Delete”.

You can change the string by modifying the value of strSearch variable in the script to suit your requirement.

Rows are deleted based on certain string

Hope this helped.

Share The Knowledge

Random Posts