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