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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
 

Forum statistics

Threads
1,140,941
Messages
5,703,301
Members
421,290
Latest member
james90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top