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
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
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
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
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