Cell Contain Certain Text Delete

[stevengoh]

My "A" cell text contains "doggy" "doga" etc and i wish i can delete the rows. But the below code can only delete the row with the exact name. Pls help.

Sub Macro1()
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i) = "dog" Then
Range("A" & i).EntireRow.Delete
End If
Next i
End Sub

 

[mumps]

When you delete rows, you must loop from the bottom up. Try:

Sub Macro1()
    Application.ScreenUpdating = False
    Dim i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If Range("A" & i) Like "*dog*" Then
           Rows(i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[stevengoh]

Thank U, it is working even without the "Application.ScreenUpdating = False"

 

[mumps]

You are very welcome. The two "Application.ScreenUpdating" lines don't affect the end result. They serve to prevent screen flickering and speed up the running of the macro. This is important if you have a large data set.

 

[stevengoh]

Very helpful. Can check with u again how to key it in formula ?

=if(A1 like "*dog*","true", "false")

 

[mumps]

Try: =IF(ISNUMBER(SEARCH("dog",A1)),"True","False")

 

[stevengoh]

Thank again.

 

[mumps]

You are very welcome.