CODEWITHSUNDEEP
excelvba
Jamie Walker
Jamie Walker

Reputation: 213

How to sort using a defined name in Excel?

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

Upvotes: 0

Views: 140

Answers (1)

Ross McConeghy
Ross McConeghy

Reputation: 874

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

Upvotes: 1

Related Questions