Delete Rows Based on Conditional Comparison in Excel VBA

Question

I am trying to create a macro in VBA in Excel that will delete the entire rows of 1 table if it does not contain a value that is listed in a range in another sheet. Here is the setup:

In sheet 1 (inventory-data), there are a set of values in column 2.

In Sheet 2 (tech_translate), there is also a set of value in Column 1.

Basically, I want the following to happen:

Check the values in all of the cells that are in column 2 on inventory-data,

Match those up against the values that are in the cells in Column 1 in tech_translate,

If there is a matching value in tech_translate, do nothing to that row in inventory-data,

If there is NOT a matching value in tech_translate, then delete the entire row in inventory-data.

I hope that makes sense so far. To help out, here is my current code that shows where I am stuck. I keep getting a type mismatch error - I know why, but cannot figure out how to fix it or what I need to do in order to get this to work.

Sub deleteTechs()

Dim LastRow As Long
Dim CompRow As Range
Dim i As Integer

With Worksheets("tech_translate")
    Set CompRow = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

LastRow = [A65536].End(xlUp).Row

For i = LastRow To 1 Step -1
    If Cells(i, 2) <> CompRow.Cells Then Rows(i & ":" & i).EntireRow.Delete
Next i

End Sub

Lance Roberts Lance Roberts · Accepted Answer · 2011-07-21T22:37:26

You can't compare a cell to a Range directly.

Use the Match function:

If IsError(Application.Match(Cells(i,2), CompRow, 0)) Then
  Rows(i & ":" & i).EntireRow.Delete
End If

Delete Rows Based on Conditional Comparison in Excel VBA

1 Answers