Hello,

I need some help setting up a macro for Excel.

Situation:
I have a list of IDs/SKUs on Sheet1, and a partial list on Sheet2. What I would like to do is cross-check the two sheets and delete all the rows on Sheet1 that's already listed on Sheet2. The IDs/SKUs are on the same column for both sheets.

Any help would be appreciated. Thank you in advance.

The following routine is a modified version of something I posted a while back. In that case, the poster want to delete rows on Sheet1 if an ID was *not* found on Sheet2.

The comment line with the *** shows the line where the change was made to delete rows on Sheet1 if the ID *was* found on Sheet2.

Explanation:

When using Set c =.Find(item):

c will contain all the attributes of the item if it is found. e.g. c.Address will return the cell address of the item, c.Font.Name will return...well, I'm sure you can figure that out.

c will be set to Nothing if it is not found.

Now, in the VBA programmers' infinite wisdom, they decided to use TRUE as the only way to check and see if an item was found.

When "If c Is Nothing" returns TRUE, the item wasn't found.

and

When "If Not c is Nothing" returns TRUE the item was found. (I would have prefered "If c is Not Nothing", but, hey, they didn't check with me first!)

So here's the code using "If Not c" to delete rows if the item is found.

Sub DeleteIDs()
'Assume Sheet1 has long list, Sheet2 has short list
'Find last piece of data in Sheet1 Column A
LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Start at bottom of Column A and get ID
For NxtID = LastRow To 1 Step -1
ID = Sheets(1).Cells(NxtID, "A")
'Look for ID in Column A of Sheet2
With Sheets(2).Range("A1:A" & LastRow)
Set c = .Find(ID, lookat:=xlWhole)
'Delete row in Sheet1 if ID is found on Sheet2
'*** Remove 'Not' to delete rows if ID is not found on Sheet2
If Not c Is Nothing Then Sheets(1).Cells(NxtID, "A").EntireRow.Delete
End With
'Move up 1 row on Sheet1 and check the next ID
Next
End Sub

I figured I just needed to change that one line. And I did try "If c Is Not Nothing..." when I was fiddling with it before I posted and obviously, that did not work.

Thank you. It worked. :)