VBA Delete rows if cell contains specific text

[kevin440red]

I found this bit of code to remove rows if it contains specific text. It runs slow on 8000 lines, would like to add more than 1 text "Dog,Cat,Chickens".
would like it to be lighting fast
Thanks

Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "[COLOR=rgb(235, 107, 86)]Dog,Cat,Chickens" IN COLUMN B
'========================================================================
    Last = Cells(Rows.Count, "B").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "B").Value) = "Dog" Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

 

[kevin440red]

Then I see no reason why it won't work. Just checking though, the word being searched for is the only text in the cell (like what your originally posted code looked for), correct?

yes thats correct
error 1004 the cell changes to #N/A Delete method Range class failed

 

[kevin440red]

Then I see no reason why it won't work. Just checking though, the word being searched for is the only text in the cell (like what your originally posted code looked for), correct?

Run-Time Error method of range class failed
the cells change to #N/A

 

[Rick Rothstein]

Do you have any Merged Cells?

 

[kevin440red]

Do you have any Merged Cells?

No cells are merged
Thank you for your patience

 

[Rick Rothstein]

Any chance you can post your workbook on DropBox (it's free), share it and post the link they give you? That way I will be able to see what is going wrong in real time. If you have any sensitive data, make sure to replace it with nonsense text.

 

[kevin440red]

Any chance you can post your workbook on DropBox (it's free), share it and post the link they give you? That way I will be able to see what is going wrong in real time. If you have any sensitive data, make sure to replace it with nonsense text.

Book 1.xlsm Shared with Dropbox www.dropbox.com

 

[Rick Rothstein]

Okay, I got your file, thanks for posting it... and I see the error you are talking about. Now I have to figure out why.

 

[kevin440red]

Then I see no reason why it won't work. Just checking though, the word being searched for is the only text in the cell (like what your originally posted code looked for), correct?

Ant luck Rick

 

[kevin440red]

Any luck Rick

 

[Rick Rothstein]

Not yet (time available for volunteering has been somewhat limited), but I think it has something to do with your table.