Excel VBA: delete rows if cells do not contain values from a table

Question

I have a data sheet with ~50,000 rows. Each row has a location identifier (column C) and other data (columns D-H). I need to delete all rows whose location identifier does not match one of 73 values in another table.

I need to use VBA (rather than formulas/conditional formatting/tables) because I'll need to repeat the action hundreds of times with new sheets.

The closest I've found is "How to delete all cells that do not contain specific values (in VBA/Excel)". However, the code provided in the answer didn't work. No errors, just nothing happened.

Grateful for any help out there!

Its less likely that a copied code will work without modification. You have to make the changes accordingly. If you have already tried please show us the code and statement which is failing. Probably then someone can peek into and suggest you to fix your problem. — Mukul Varshney

Jeremy Jeremy · Accepted Answer · 2017-02-03T10:04:41

Here you go:

Sub Remove_Rows()
Dim i As Long

i = Range("C" & Cells.Rows.Count).End(xlUp).Row ' Find the bottom row number

Do Until i = 1 ' This loops to the top row before stopping (assuming you have a header row that you want to keep)
    If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A:A"), Cells(i, 3)) = 0 Then Cells(i, 1).EntireRow.Delete 'This checks to see if the value in the C column is in sheet2 - If not, deletes row
    i = i - 1 'This step backwards though you data rows - the reason to use this as opposed to "forward stepping" is to avoid issues causes on the count when a row is deleted
Loop

End Sub

Excel VBA: delete rows if cells do not contain values from a table

1 Answers