VBA script to search for a string in a column and delete an entire row containing that string?

Question

I have some 100 rows with some 10 columns.I want to delete the entire row if the cell in column B(or 2) contains a specific string "XYZ" (say)

The below code works perfect if the string is present in the beginning. But I want it to work for below cases as well:

"XYZadjsaoda" (Case 1: Beginning of the string)
"asdsalXYZasdsa" (Case 2: Middle of the string )
"dsadsad32XYZ" (Case 3: End of the string)

If the string is present it should delete the entire row.

Sub DeleteRowBasedOnCriteria()
    Dim RowToTest As Long
    Sheets("Jira").Select
    For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
        With Cells(RowToTest, 2)
            If .Value = "XYZ" _        
            Then _
            Rows(RowToTest).EntireRow.Delete
        End With
    Next RowToTest
End Sub

The function should match XYZ irrespective of the location inside a cell and delete entire row.

I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 14 '19 at 12:47

SJR · Accepted Answer · 2019-01-14T12:37:01.153

3

Use the Like operator with wildcards (possibly with a case qualification too).

AutoFilter might be a better option though.

Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
    With Cells(RowToTest, 2)
        If .Value Like "*XYZ*" Then Rows(RowToTest).EntireRow.Delete
        'If ucase(.Value) Like "*XYZ*" Then Rows(RowToTest).EntireRow.Delete  CASE INSENSITIVE 
    End With
Next RowToTest
End Sub

edited Jan 14 '19 at 12:37

answered Jan 14 '19 at 12:31

SJR

22,986
6
18
26

1

had been stuck with this for a week! – Abhishek Singh Jan 14 '19 at 12:48

Pᴇʜ · Answer 2 · 2019-01-14T12:41:57.620

3

Alternatively to the Like operator you can use the InStr function:

If InStr(1, "XYZ", .Value, vbTextCompare) > 0 Then

You can use …

vbTextCompare to make it not case sensitive.
vbBinaryCompare to make it case sensitive.

edited Jan 14 '19 at 12:41

answered Jan 14 '19 at 12:34

Pᴇʜ

56,719
10
49
73

VBA script to search for a string in a column and delete an entire row containing that string?

2 Answers2