Find matches in column and delete the entire row where found

[Adrae]

Anyone know what the easiest way to do this is.? I am hoping there is a faster more efficient way to search for ce.Offset(,5) in these two ranges and delete the rows without doing the whole ce2 & ce3 For Each Loop. Thanks!

Dim ce as Range
Dim ce2 as Range
Dim ce3 as Range

For each ce in Range("include")
If ce = "x" Then

Sheets(Sheet1).Activate
For each ce2 in Range ("z6:z50000")
If ce2 = ce.Offset(,5) Then
ce2.EntireRow.Delete
End If
Next ce2

Sheets(Sheet2).Activate
For each ce3 in Range ("z6:z50000")
If ce3 = ce.Offset(,5) Then
ce3.EntireRow.Delete
End If
Next ce3

End If
Next ce

 

[Von Pookie]

You could try the autofilter. This is not well-tested, but give it a go:

Sub test()
Dim lRow As Long, ws As Worksheet
Dim c As Range, Val As String

Application.ScreenUpdating = False

'loop through the range
For Each c In Range("include")
    If c = "x" Then 'if cell contains 'x'
        'save value from cell 5 columns to the right
        Val = c.Offset(, 5).Value
        
        'loop through Sheet1 and Sheet2
        For Each ws In Sheets(Array("Sheet1", "Sheet2"))
            With ws
                'if autofilter is not on, activate it in column Z only
                If .AutoFilterMode = False Then .Range("Z:Z").AutoFilter
                
                'filter the column to display the rows that match 'Val'
                .Range("Z:Z").AutoFilter field:=1, Criteria1:=Val
                
                'delete only rows with visible cells within range Z6:Z50000
                .Range("Z6:Z50000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
                
                'turn off autofilter
                .Range("Z:Z").AutoFilter
            End With
        Next ws 'loop to next sheet
    End If
Next c
            
Application.ScreenUpdating = True

End Sub