How can I delete rows via comparison using VBA?

[jmckeone]

I have two tabs in a workbook. Each has a column containing a unique numerical identifier. What I want to do is create a macro that will loop through the column on tabA, verify it against the column on tabB and when a match occurs delete the row on tabA. The end result should be that only the new items on tabA will remain. Let me know if this is sufficiently clear or still a bit vague. Thanks.

 

[Boller]

Here's a way without a macro :

- In the first cell of a blank column on Sheet1 enter this formula and fill down : =VLOOKUP(A1,Sheet2!A:A,1,0)

- Select the column and go to Edit>GoTo>Special>Formulas>Numbers>Text>OK

- Go to Edit>Delete>EntireRow

- Delete the column

 

[jmckeone]

Boller, thanks for the suggestion. All else fails I'll go the formula route.

Meanwhile, I've tried every suggestion posted thus far and every one puts up an error of one type or another. Since I'd not put up an image of the layout I thought I'd give it another go. Sheet1 has the current items and Sheet2 are those to delete on sheet1.

 

[mikerickson]

What error did my code cause?
other than this line (now corrected) not supporting "that property or method"

Sheets("sheet1").UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Boller]

Boller, thanks for the suggestion. All else fails I'll go the formula route.

Here's the code based on the steps I set out in my previous post :-

Dim rng As Range
With Sheets("Sheet1")
    .[A:A].Insert
    Set rng = .Range(.[A2], .[B65536].End(xlUp)(1, 0))
    rng.FormulaR1C1 = "=VLOOKUP(RC[1],Sheet2!C1,1,0)"
    On Error Resume Next
    rng.SpecialCells(xlCellTypeFormulas, 3).EntireRow.Delete
    On Error GoTo 0
    .[A:A].Delete
End With

 

[jmckeone]

MikeRickson ... in answer to your question:

Just rechecked your code and that adjusted line you posted. When I added an S onto SpecialCell and reran the code it worked. Thanks!

Boller, your code worked as well.

Now for the tough question ... can either of you explain the logic/methodology of your code in terms a newbie can understand. It is great having code that works though in time I hope to understand how it works well enough to use the various components as building blocks for other tasks.

 

[Boller]

jmckeone

The code I posted does the same thing as the manual steps I set out.

Step through the code line by line via F8 and you will see what it does.

 

[mikerickson]

My code uses the Advanced Filter. Using the Sheet2 list as the criteria range, it filters List 1 to show only those entries that are also in List 2.
The SpecialCells selects the visible cells (those that have a match in List 2) and deletes those rows. There is a bit of fiddling to restore the header row after deleteion, but that is the main logic of my code.