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

Answer 2

To my knowledge, finding the cell does not mean it's the active cell.

For Any cell in sheet

Public Sub Test()
 Sheet1.Cells.Find("CY*").EntireRow.Delete
End Sub

Your Case (Just checking Column C up to row lrow)

Public Sub Test()
 ActiveSheet.Range("C2:C" & lrow).Find("CY*").EntireRow.Delete
End Sub

Answer 3

This should be good for small datasets. If you are using a larger set autofilter would be faster.

     With ("C2:C" & 1row)
        Do
        Set rngFind = .Find(What:="CY*", After:=.Cells(1, 1), LookIn:=xlFormulas, _
                            LookAt:=xlPart, SearchOrder:=xlByRows, 
                            SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False)
        If Not rngFind Is Nothing Then Exit Do
        rngFind.EntireRow.Delete
    Loop
End With

Answer 4

Here is another solution that I Just tested. It will work for multiple sheets, modify to suit your needs.

Sub FindCY()
Dim WS As Worksheet
Dim Rng1 As Range
Dim cell As Range
Dim CYFind As Variant
CYFind = "CY*"

On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
Set Rng1 = WS.Range("C1").EntireColumn
For Each cell In Rng1
If cell.Value = CYFind Then
Rng1.EntireRow.Delete
End If
Next cell
Next WS

End Sub