VBA to delete rows *partial text*

[jsabo]

I have seen a lot of VBA out there for searching a column and deleting rows which are empty, or deleting ones where criteria is not met. I have a slightly different request.

I would like to do much the same, but have the VBA look at a partial string. I.e, I would like for it to look for "BOB-SMITH" and keep those rows where the criteria is met. However, this database lacks any sort of control, so people may put "BOB-SMITH2". In the event that the script finds "BOB-SMITH2" where the criteria is "BOB-SMITH", then it will trim down "BOB-SMITH2" to match the criteria. I would also prefer that it trims from both directions, if need be. Then, as most instances of a script like this, I would like it to delete rows where the criteria doesn't match/the field is null. Is something like this possible?

Thanks in advance!

 

[My Aswer Is This]

What field are we going to look in?
And you want all rows that have "Bob Smith" Deleted is that correct?

 

[jsabo]

What field are we going to look in?
And you want all rows that have "Bob Smith" Deleted is that correct?

I want it to look through all of column D. Let's say the criteria is "BOB-SMITH". The script might encounter "BOB-SMITH-PITTSBURGH". In this case, I want it to trim from the right to match "BOB-SMITH". The script might encounter "PITTSBURGH-BOB-SMITH". In this case, I would like it to trim from the left to match. Keep any rows which contain "BOB-SMITH". Delete any rows which don't match the criteria or are blanks.

 

[My Aswer Is This]

Try this:

Sub Bob_Smith()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If InStr(Cells(i, 4).Value, "BOB-SMITH") Then

        Else
            Rows(i).Delete
        End If
    Next
Application.ScreenUpdating = True
End Sub