How to sort using a defined name in Excel?

Question

I have a Macro that sorts a database column by the category name with a custom order. I'm not sure if it is possible but I would like to use a defined name that contains all of the category names that is called Sorting_List. I would like for the defined name Sorting_List to replace the "FL,MF,HF,Furnace Eng,Launder,CM,CM Eng,ML,PP,Lab SOT,Lab AT,SSP,SII,NAPS,ES,FM,PW,C2H2,CA,Fluids,Fluids Eng,Elec,PMDA,Elec Eng,CH SW,CH SII,CH Std,CH High,CH Elec,CH Eng,CH FS,FS SW,FS SII,FS Elec,Eng,Train,Lab,Lab SW" so if I ever change anything in that defined name list it will automatically change in my macro. Below is my code. Any help is much appreciated.

  ActiveWorkbook.Worksheets("Database").ListObjects("Database").Sort.SortFields. _
    Add Key:=Range("Database[Category]"), SortOn:=xlSortOnValues, Order:= _
    xlAscending, CustomOrder:= _
    "FL,MF,HF,Furnace Eng,Launder,CM,CM Eng,ML,PP,Lab SOT,Lab AT,SSP,SII,NAPS,ES,FM,PW,C2H2,CA,Fluids,Fluids Eng,Elec,PMDA,Elec Eng,CH SW,CH SII,CH Std,CH High,CH Elec,CH Eng,CH FS,FS SW,FS SII,FS Elec,Eng,Train,Lab,Lab SW" _
    , DataOption:=xlSortNormal

Ross McConeghy · Accepted Answer · 2014-08-21 18:17:56Z

1

Dim sCustomOrder as String
Dim rng as Range
sCustomOrder = ""
For Each rng in ActiveWorkbook.Names("Sorting_List").RefersToRange
    If rng.Text <> "" Then sCustomOrder = sCustomOrder & "," & rng.Text
Next rng

ActiveWorkbook.Worksheets("Database").ListObjects("Database").Sort.SortFields.Add _
    Key:=Range("Database[Category]"), _
    SortOn:=xlSortOnValues, _
    Order:= xlAscending, _
    CustomOrder:= sCustomOrder, _
    DataOption:=xlSortNormal

answered Aug 21, 2014 at 18:17

Ross McConeghy

8742 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie Walker Over a year ago

Thanks for the response. However, it keeps debugging at the ActiveWorkbook.Worksheets line

Ross McConeghy Over a year ago

The only way anyone can be helpful here is to know exactly what is happening. What exactly is the error message that is being displayed before you click "Debug"?

Collectives™ on Stack Overflow

How to sort using a defined name in Excel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related