Dynamic VBA Delete based on cell not containing Part of a text

Question

Trying to do a dynamic row removal for values in Column C where they value does not contain CY*. I have the below code but keep getting a If Block Error. Anyone know how to make this work?

Dim ContainWord As String
On Error Resume Next
Range("C2:C" & lrow).Select
ContainWord = "CY*"
If Not Cell.Find(ContainWord) Then ActiveCell.EntireRow.Delete.Row
End If

lrow is defined earlier in my code as: Dim lrow As Long lrow = Range("A" & Rows.Count).End(xlUp).Row

Answer 1

You could use autofilter and then delete visible cells

Option Explicit
Public Sub test()
    Dim lRow As Long
    With ActiveSheet
        .AutoFilterMode = False
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        With .Range("A1:Y" & lRow)
            .AutoFilter Field:=3, Criteria1:="<>CY*"
            On Error Resume Next
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
     .AutoFilterMode = False
    End With      
End Sub