Delete Range if contains String

[JaysonHughes]

If Sheet "40"



if column BB Contains "Small" or "Medium" or "Large" or "HeavyBulky"

Delete AM:BD

NOT EntireRow

If have the following but it dose not loop and delete the EntireRow:


Sub TSI_Keep_Decant()
'
Dim SheetName As Long
'
Application.ScreenUpdating = False
'
For SheetName = 40 To 40 '
On Error GoTo NotFound
With Sheets(CStr(SheetName))
With .Range("BB2:BB" & .Range("B" & .Rows.Count).End(xlUp).Row)
.Value = Sheets(CStr(SheetName)).Evaluate("IF(" & .Address & "=""Small""," & .Address & "=""Medium""," & .Address & "=""Large""," & .Address & "=""HeavyBulky""," & .Address & ",""#N/A"")") '
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
End With
NotFound:
On Error GoTo -1
Next

Application.ScreenUpdating = True
End Sub

 

[JaysonHughes]

 

[JaysonHughes]

So if BB = Small" or "Medium" or "Large" or "HeavyBulky" delete between AM:BD on the row.