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

taylorbjorklund

New Member
Joined
Aug 22, 2014
Messages
3
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.

Product SKUDescriptionSoldDaily AvgStock OutIn StockOn OrderOn Back Order
ACCIAF000664Alcatel 510A PIB w/ $15 airtime60.273.67100
ACCIAT000898Alcatel 871A (Gray) PIB w/ $25 airtime00100
ACCIAM001059Amazon Fire 32GB00000
ACCIAM001060Amazon Fire 64GB10.050000
ACCIAF000757Amazon Kindle Fire HD 8.9" LTE00000
ACCIAP000894Apple iPhone 4S 8GB Black00100
ACCIAP000895Apple iPhone 4S 8GB White00200
ACCIAP000883Apple iPhone 5C 16GB Blue20.090000
ACCIAP000884Apple iPhone 5C 16GB Green10.0522100
ACCIAP000881Apple iPhone 5C 16GB Pink20.090000
ACCIAP000880Apple iPhone 5C 16GB White00100
ACCIAP000882Apple iPhone 5C 16GB Yellow10.050000
ACCIAP000878Apple iPhone 5C 32GB Blue00000
ACCIAP000879Apple iPhone 5C 32GB Green00000
ACCIAP000876Apple iPhone 5C 32GB Pink00000
ACCIAP000875Apple iPhone 5C 32GB White00000
ACCIAP000877Apple iPhone 5C 32GB Yellow00000
ACCIAP000891Apple iPhone 5S 16GB Gold50.2313.2300
ACCIAP000892Apple iPhone 5S 16GB Silver20.090000
ACCIAP000893Apple iPhone 5S 16GB Space Gray40.185.5100
ACCIAP000888Apple iPhone 5S 32GB Gold00100

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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;

Code:
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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
Back
Top