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>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Alan_P

Well-known Member
Joined
Jul 8, 2014
Messages
596
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.
 

taylorbjorklund

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

Watch MrExcel Video

Forum statistics

Threads
1,095,314
Messages
5,443,749
Members
405,248
Latest member
samunders

This Week's Hot Topics

Top