Broken drop-down list due to its contents being changed with a macro

[mdovey]

I have set up drop-down lists on the "Hardware" sheet which look at my stock tables on the "Stock Sorting" sheet and makes the table corresponding to the table the drop down menu is housed in their range to give the user an option of which product they want to select.

I currently have two stock systems: the first stock system I have is on the "Stock Table" sheet which is one table that holds all of the stock I have. My second stock system is on the "Stock Sorting" sheet which looks at the first stock table on the "Stock Table" sheet and separates the stock into tables for each product type. For example the stock table on the "Stock Table" sheet will hold all of the motherboards, CPUs, GPUs ETC.... the second stock system on the "Stock Sorting" sheet looks at first table and pulls out all of the motherboards and puts them in their own table, all of the CPUs and puts them in their own table ETC...

The VBA code I am using to update the second stock system every time stock is added to the first seems to ruin the drop-down lists I am using. The code looks at the first table on the "Stock Table" sheet and for the motherboard table on the "Stock Sorting" sheet it copies all of the motherboards and pastes them over the contents of the the table so if motherboards have been taken away from the main table the motherboard table will loose those motherboards and if motherboards have been added to the first table the motherboard table will gain those motherboards. When this is done the drop-down lists which are using the tables on the "Stock Sorting" sheet as their range are broken and when looking at what their range is set at this error is displayed "='Stock Sorting'!#REF!". The drop-down lists int the hardware section of the "hardware" sheet are meant to have this range when not broken "='Stock Sorting'!$C$5:$C$1000". The code below is triggered every time the user form to add stock is used. Is there anyway from stopping the drop-down menus range from being broken or code that updates the range when the code that breaks them is used?

Here is the workbook and code to allow you to help me and understand the problem:

Drop-Down Lists.xlsm

Sub StockSorter()
Sheets("Stock Sorting").Range("A4:A1000000").EntireRow.Delete
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("B1:B2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("B4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("K1:K2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("K4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("T1:T2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("T4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("AC1:AC2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("AC4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("AL1:AL2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("AL4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("AU1:AU2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("AU4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("BD1:BD2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("BD4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("BM1:BM2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("BM4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("BV1:BV2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("BV4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("CE1:CE2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("CE4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("CN1:CN2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("CN4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("CW1:CW2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("CW4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("DF1:DF2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("DF4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("DO1:DO2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("DO4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("DX1:DX2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("DX4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("EG1:EG2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("EG4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("EP1:EP2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("EP4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("EY1:EY2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("EY4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("FH1:FH2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("FH4"), _
    Unique:=False
Worksheets("Stock Table").Range("A3").CurrentRegion.Offset(0, 2).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Stock Sorting").Range("FQ1:FQ2"), _
    CopyToRange:=Sheets("Stock Sorting").Range("FQ4"), _
    Unique:=False

End Sub

[Richard Buttrey]

Hi,

I don't have access to Excel just at the moment but are your drop down lists using defined range names? Maybe in deleting A4:A1000000 you are deleting a range name.

Incidentally you seem to have uploaded a .xlsx file rather than a .xlsm one.

One other point. Since your large macro contains subsections which are essentially the same apart from a different column ref it would be more efficient to create an array of these column letters and then have a loop which loops 20 times and use the loop counter to pick the column ref from the array each time.

[mdovey]

Hello,
No the drop down lists are using code such as "='Stock Sorting'!$C$5:$C$1000" so they are referring to the cells. Im not sure if using the code I have posted above deletes the cells completly for a breif second which is causing the range to be broken as it can no longer find the cells and doesnt automaticly update the link when the cells come back. Is there different code I could be using such as a Clear command rather than Delete? This could fix the problem of the range being broken. Maybe even by using named ranges instead that might fix the problem?

Thank you for pointing out my attachment I managed to upload the wrong version. I have fixed it now and the correct workbook is uploaded.

The array probably would be much more efficient but I am fairly new to VBA so am only good with basic coding and having people suggest code to use. An array does sound like a much cleaner and more efficient way but for now I guess its not my priority so I am happy to deal with it until I get problems such as this one out of the way.

[Richard Buttrey]

Hi,

As I thought, it's because you are deleting row 4:1000000 which includes within it your validation drop down definitions. Instead of deleting rows use

Sheets("Stock Sorting").Range("A4:A1000000").EntireRow.ClearContents

As I say the current code could be much simplified so when you are ready repost and no doubt someone will sort it out.

[mdovey]

Thank you very much this has helped out a lot. Thanks you for the extra advise as well, once all of the main parts are ironed out I will come back and get someone to help out with the array.