Delete rows that don't match a table

[outlawspeeder]

I am trying to delete rows that A:A does not match a list on another tab.

I know I can do a add column, the vlookup, then delete the empty rows but I am trying to do it a cleaner way.

 

[mikerickson]

If the data that you want to delete
1) has unique headers on each row
2) no (completely) blank rows

Then Advanced Filter will work.
If your list is on Sheet2!BB:BB , a two row, one column criteria range with the top cell empty and the bottom cell holding the formula
=ISNA(MATCH(A2,Sheet2!BB:BB,0))
will filter to show those rows where the column A entry is not in Sheet2!BB:BB.
These can then be easily deleted.

 

[crimson_b1ade]

here's a vba solution. of course you'll need to change the default sheet names to whatever yours are (herein amed as...Sheet2 (the delete sheet) and Sheet3 (the lookup sheet)) :

Sub DeleteMeAlready()
 
On Error Resume Next
 
Dim dSheet As Worksheet
Dim lSheet As Worksheet
Dim wsf As WorksheetFunction
Dim LastRow As Long, i As Long
 
Set dSheet = Sheets("Sheet2") 'this is the delete sheet2 - you'll need to change the sheet name within the quotes to whatever yours are named
Set lSheet = Sheets("Sheet3")  'this is the lookup sheet3 - - you'll need to change the sheet name within the quotes to whatever yours are named
 
Set wsf = Application.WorksheetFunction
 
LastRow = dSheet.Range("A" & Rows.Count).End(xlUp).Row
 
For i = LastRow To 1 Step -1
 
If IsError(wsf.Match(dSheet.Range("A" & i), lSheet.Range("A:A"), 0)) = True Then
   Rows(i).EntireRow.Delete
End If
 
Next
 
End Sub

 

[outlawspeeder]

That worked great. I did do a one mod

If IsError(wsf.Match(dSheet.Range("A" & i), lSheet.Range("B10:B50"), 0)) = True Then
Rows(i).EntireRow.Delete

("B10:B50") this will only pull from this.

Thanks for the help