Like Function Not Working for All Cells

[dannie]

Hello,

I am using the Like function, but it isn't working on all of the cells.
column B has company names. I am trying to get rid of names that have the word sample in them.
my code:
'getting rid of sample plans
For Each cell In Range("B2:B" & lastrow)
If cell Like "*Sample*" Then
cell.EntireRow.Delete
End If
Next

it gets rid of only some of the plans with the word sample. any help is greatly appreciated!

Thanks,
D

 

[jeffreybrown]

Try it with a filter instead of looping, plus, when deleting, you need to work backwards in a loop as the rows will always change.

Sub DelRows2()
    Application.ScreenUpdating = False
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter Field:=1, Criteria1:="*Sample*"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

You need to loop backwards when deleting rows. You can't use a For Each ... Next construct or some rows will be skipped.

 

[dannie]

thanks guys!! worked great. that makes this and other projects much simpler. i appreciate it!
D

 

[jeffreybrown]

You are very welcome and thanks for the feedback. We are happy to help.