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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

HalfAce

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

Forum statistics

Threads
1,141,218
Messages
5,705,076
Members
421,377
Latest member
FerdiFuchs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top