Find matches in column and delete the entire row where found

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You could try the autofilter. This is not well-tested, but give it a go:

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

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top