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

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteRowWithContents()
  Dim V As Variant
  For Each V In Array("Dog", "Cat", "Chickens")
    Columns("B").Replace V, "#N/A", xlWhole, , True, , False, False
  Next
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

 

[kevin440red]

Give this macro a try...

Sub DeleteRowWithContents()
  Dim V As Variant
  For Each V In Array("Dog", "Cat", "Chickens")
    Columns("B").Replace V, "#N/A", xlWhole, , True, , False, False
  Next
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End Sub

getting error on line 6

Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

 

[Rick Rothstein]

What is the error number and error description? By the way, I test the code and it worked on my test.

 

[kevin440red]

What is the error number and error description? By the way, I test the code and it worked on my test.

Sorry the code works on a new sheet the one I'm running it in has formulas built in they may be the issue.
error # 1004

 

[Rick Rothstein]

Yes, if Column B has formulas in it, the method I posted won't work.

 

[kevin440red]

Yes, if Column B has formulas in it, the method I posted won't work.

Column B has no formulas but other columns do

 

[Rick Rothstein]

Does Column B have any of the keywords being searched for (Cat, Dog or Chickens)?

 

[kevin440red]

Does Column B have any of the keywords being searched for (Cat, Dog or Chickens)?

Yes

 

[Rick Rothstein]

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?