How to use one master sheet to delete rows from weekly updates

[taylorbjorklund]

My title sounds confusing. Essentially, I have a list of products that I pull weekly from a database and it contains all the products, active and discontinued. When I'm doing inventory ordering, I'd like to use a master "discontinued" list to filter out all the product that is no longer active.

For example, within the following table let's say the Amazon Fire SKU's are discontinued. Every time I pull this report, they'll be there. I'd like to find a way to have a running list that I can add to every time a product is discontinued, and then when I pull this list, I run the Macro and BAM, they disappear.

I will warn anyone kind enough to help me with this that I have zero experience with VBA, although I can wrap my head around formulas, and recording macros. Thank you so much.

[TABLE="width: 936"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Product SKU[/TD]
[TD]Description[/TD]
[TD]Sold[/TD]
[TD]Daily Avg[/TD]
[TD]Stock Out[/TD]
[TD]In Stock[/TD]
[TD]On Order[/TD]
[TD]On Back Order[/TD]
[/TR]
[TR]
[TD]ACCIAF000664[/TD]
[TD]Alcatel 510A PIB w/ $15 airtime[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.27[/TD]
[TD="align: right"]3.67[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAT000898[/TD]
[TD]Alcatel 871A (Gray) PIB w/ $25 airtime[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAM001059[/TD]
[TD]Amazon Fire 32GB[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAM001060[/TD]
[TD]Amazon Fire 64GB[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAF000757[/TD]
[TD]Amazon Kindle Fire HD 8.9" LTE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000894[/TD]
[TD]Apple iPhone 4S 8GB Black[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000895[/TD]
[TD]Apple iPhone 4S 8GB White[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000883[/TD]
[TD]Apple iPhone 5C 16GB Blue[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000884[/TD]
[TD]Apple iPhone 5C 16GB Green[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000881[/TD]
[TD]Apple iPhone 5C 16GB Pink[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000880[/TD]
[TD]Apple iPhone 5C 16GB White[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000882[/TD]
[TD]Apple iPhone 5C 16GB Yellow[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000878[/TD]
[TD]Apple iPhone 5C 32GB Blue[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000879[/TD]
[TD]Apple iPhone 5C 32GB Green[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000876[/TD]
[TD]Apple iPhone 5C 32GB Pink[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000875[/TD]
[TD]Apple iPhone 5C 32GB White[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000877[/TD]
[TD]Apple iPhone 5C 32GB Yellow[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000891[/TD]
[TD]Apple iPhone 5S 16GB Gold[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.23[/TD]
[TD="align: right"]13.2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000892[/TD]
[TD]Apple iPhone 5S 16GB Silver[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000893[/TD]
[TD]Apple iPhone 5S 16GB Space Gray[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.18[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]ACCIAP000888[/TD]
[TD]Apple iPhone 5S 32GB Gold[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Alan_P]

Hi Taylor,

With the following assumptions; your list is in sheet 1, the column with the SKU's is "A", in sheet 2 column "A" there is a list of discontinued SKU's, when you say "BAM, they disappear" you mean deleted.

Please be sure to test this in a copy of your workbook first!

With a copy of your worksheet open press ALT + F11, click Insert > Module, paste this code in;

Sub Discontinued()

Dim LastRow1 As Long
Dim LastRow2 As Long
Dim i As Long
Dim n As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

LastRow2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
LastRow1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow2

    n = Sheets(2).Cells(i, 1).Text
           
    Sheets(1).UsedRange.AutoFilter Field:=1, Criteria1:=n
    Sheets(1).Range("A2:A" & LastRow1).SpecialCells(xlCellTypeVisible).Delete

Next

Sheets(1).AutoFilterMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hope this helps,
Cheers,
Alan.

 

[taylorbjorklund]

Your assumptions are correct. I'll try this and come back with the results. Thank You!

 

[taylorbjorklund]

I received a "Run-time error '9':
Subscript out of range

Was there something in the code that I was supposed to change, like a sheet name, or some other variable?

 

[Alan_P]

When you hit debug which line comes up in yellow?