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.
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.
Step 2
Insert a module in which we will write the program.
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.
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.
Hope this helped.
