# Delete row if value in table

#### coco803

##### New Member
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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 _
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``````

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!

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.

Replies
1
Views
145
Replies
2
Views
232
Replies
2
Views
121
Replies
9
Views
275
Replies
3
Views
157

1,219,935
Messages
6,151,062
Members
451,006
Latest member
dhinze84

### 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.

### Which adblocker are you using?

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

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