Delete row if value in table

coco803

New Member
Joined
May 26, 2006
Messages
32
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?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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?)
Code:
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

New Member
Joined
May 26, 2006
Messages
32
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,983
Messages
5,526,056
Members
409,682
Latest member
HisHailo

This Week's Hot Topics

Top