Delete row if value in table

[coco803]

I have searched the message board and get close to what I need but still need help. I have 2 sheets (sheet 1 and sheet 2). Sheet 1 has a list of products with product # in column A and other product data in columns B-L. Sheet 2 has one column of discontinued products. I would like to run a macro that will delete any row on sheet 1 that has a discontinued product from sheet 2. Sounds easy enough and I have written code to delete blank rows or rows that meet one particular criteria. But in this case, I cannot figure out how to have the macro search the list on sheet 2. I tried using WorksheetFunction(Vlookup) but have not had any success. Can someone help?

 

[HalfAce]

Hello coco803,
Providing the list of discontinued part numbers on sheet2 is in column A
this should help.
Any value found in column A of sheet1 that is also found in column A of sheet2 will
have it's entire row deleted. (That sound about right?)

Option Explicit

Sub DeleteItems()
Dim Item As Range, _
    DiscontinuedList As Object, _
    Rw As Long
    
Set DiscontinuedList = CreateObject("Scripting.Dictionary")

With Sheets("Sheet2")
  For Each Item In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    If Not DiscontinuedList.Exists(Item.Value) Then _
      DiscontinuedList.Add Item.Value, Nothing
  Next
End With

With Sheets("Sheet1")
  For Rw = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
    If DiscontinuedList.Exists(.Cells(Rw, "A").Value) Then .Rows(Rw).Delete
  Next
End With

Set DiscontinuedList = Nothing

End Sub

 

[coco803]

Thanks HalfAce, it worked perfectly! I am not sure what some of the code means, but it does exactly what I want it to do. Guess I need to try to break it down and find out what the parts mean so I actually learn something! Thanks again!

 

[HalfAce]

You're most welcome. (Glad it helped.)
Just post back with whatever part(s) you're curious about & I'll see if I can make it make sense.