# Find matches in column and delete the entire row where found

##### Active Member
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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``````

Replies
4
Views
348
Replies
1
Views
238
Replies
6
Views
221
Replies
11
Views
350
Replies
1
Views
186

1,219,898
Messages
6,150,837
Members
450,987
Latest member
PopeScooby

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

### Which adblocker are you using?

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

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