VBA for deleting a row when the column contains (not exact match)

[garypea123]

Hi,

I have the below VBA which will look at Column A and delete any row that has an exact match for Vacant

Is there a simple way I can get this to delete a row which contains the word Vacant (so Vacant 1, Vacant 2 etc etc). So pretty much a partial match but only if the word "Vacant" appears in the column.

Thanks
Gary

 

[Fluff]

How about

If Cells(i, 1) Like "*Vacant*" Then

 

[garypea123]

I have tried to apply the below, but it is not deleting anything.

I think I am likely doing something wrong here.

In column B if it contains the word Gary I would like it to delete that row.

Sub Delete_Gary()


Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "Gary" Then
Cells(i, 2).EntireRow.Delete
End If
Next i

End Sub

 

[Fluff]

You need the * at the end like "Gary*"

 

[Peter_SSs]

Try this change

If Cells(i, 2) Like "Gary" Then
Cells(i, 2).EntireRow.Delete
End If

If LCase(Cells(i, 2).Value) Like "*gary*" Then Rows(i).Delete

 

[garypea123]

You need the * at the end like "Gary*"

Yes, that makes sense. And works like a dream

 

[garypea123]

Try this change

If Cells(i, 2) Like "Gary" Then
Cells(i, 2).EntireRow.Delete
End If

If LCase(Cells(i, 2).Value) Like "*gary*" Then Rows(i).Delete

Thanks, I tried this code also, and works like a dream also

Thank you both very much. I am new to this, and so struggle sometimes as I have no official lessons. Just google to help me

 

[Fluff]

Glad we could help & thanks for the feedback.