Create a dynamic custom sort VBA Excel

Question

I am sure there is a post covering this and I am just not finding it, so apologies for any repost.

I have a range of data on one sheet. I have a dynamic list of the order I need the data to be sorted by in a custom sort on another sheet. I am trying to figure out how to create a custom sort list in based on the data in sheet two to sort the data from Sheet one by. The closest I was able to come up with is defining the 28 sorts and assigning them to variables. However the list never actually sorts

    Application.CutCopyMode = False
Application.AddCustomList ListArray:=Array(FC1, FC2, FC3, FC4, FC5, _
    FC6, FC7, FC8, FC9, FC10, FC11, FC12, FC13, FC14, FC15, FC16, FC17 _
    , FC18, FC19, FC20, FC21, FC22, FC23, FC24, FC25, FC26, FC27, FC28)
ActiveWorkbook.Worksheets("Pick List").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Pick List").Sort.SortFields.Add Key:=Range( _
    "F2:F300000"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
    "FC1 , FC2,FC3,FC4,FC5,Fc6,FC7,FC8,FC9,FC10,FC11,FC12,FC13,FC14,FC15,FC16,FC17,FC18,FC19,FC20,FC21,FC22,FC23,FC24,FC25,FC26,FC27,FC28" _
    , DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Pick List").Sort
    .SetRange Range("A1:V300000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

In my debugging attempts I suspect my issue stems from the line:

CustomOrder:= _
    "FC1 , FC2,FC3,FC4,FC5,Fc6,FC7,FC8,FC9,FC10,FC11,FC12,FC13,FC14,FC15,FC16,FC17,FC18,FC19,FC20,FC21,FC22,FC23,FC24,FC25,FC26,FC27,FC28" _

The Array recognizes each variable as it's value. However the custom order does not. I am assuming, because it is in quotes and is looking at it like a string.

You actually have 30 declared variables named FCxx? In any case, Excel knows nothing about the VBA runtime context / your variables; F18 is only meaningful in VBA, depending on the scope of the variables' declarations. — Mathieu Guindon
– Mathieu Guindon, Commented May 3, 2018 at 17:44
There are 1-28, That would be 28 variables. The line that I highlighted in my post the FCxx do not read as variables when debugging. — viper941
– viper941, Commented May 3, 2018 at 18:04

Mathieu Guindon · Accepted Answer · 2018-05-03 18:45:44Z

Assuming these 28 variables hold your custom sort order, what you want is to make a string out of the values held by these identifiers.

CustomOrder:= _
    "FC1 , FC2,FC3,FC4,FC5,Fc6,FC7,FC8,FC9,FC10,FC11,FC12,FC13,FC14,FC15,FC16,FC17,FC18,FC19,FC20,FC21,FC22,FC23,FC24,FC25,FC26,FC27,FC28"

That's making a string out of the identifiers themselves - IOW your intuition is correct.

So instead of re-listing the 28 variables into a string literal, use the part of HackSlash's answer that sets the CustomOrder index:

CustomOrder:=Application.CustomListCount

That should work as long as the last added custom list is the custom list you just defined and added.

So:

Application.AddCustomList _
    ListArray:=Array(FC1, FC2, FC3, FC4, FC5, FC6, FC7, _
                     FC8, FC9, FC10, FC11, FC12, FC13, FC14, _
                     FC15, FC16, FC17, FC18, FC19, FC20, FC21, _
                     FC22, FC23, FC24, FC25, FC26, FC27, FC28)

Dim pickList As Worksheet
Set pickList = ActiveWorkbook.Worksheets("Pick List")

pickList.Sort.SortFields.Clear
pickList.Sort.SortFields.Add _
    Key:=pickList.Range("F2:F300000"), _
    SortOn:=xlSortOnValues, _
    Order:=xlAscending, _
    CustomOrder:=Application.CustomListCount, _
    DataOption:=xlSortNormal

With pickList.Sort
    .SetRange pickList.Range("A1:V300000")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

That said, having 28 variables with essentially the same name plus a numeric suffix, is a code smell begging for a data structure: instead of 28 variables, you should have one variable containing a single-dimensional array with 28 slots.

Dim fcValues(1 To 28)
fcValues(1) = "the value you gave to FC1"
fcValues(2) = "the value you gave to FC2"
'...
fcValues(28) = "the value you gave to FC28"

If the variables' values came from a worksheet, then its even easier (assuming a single-column sourceRange):

Dim fcValues As Variant
fcValues = Application.Transpose(sourceRange.Value)

Such an array could be passed as-is to the ListArray argument of AddCustomList:

Application.AddCustomList ListArray:=fcValues

HackSlash · Accepted Answer · 2018-05-04 15:52:57Z

1

If you have 28 variables that you want to name 1 to 28 you should make an array. Then you can just pass that to the ListArray parameter.

Now, we can address the fact that CustomOrder is a variant that can accept a string or an integer. Nobody seems to know why because the M$ documentation is lacking on this property. From what I can tell the code below will work because we've added a new customlist and then we select the last one by citing the number of total lists as the CustomOrder.

Option Explicit

Sub CustomSort()
    Application.CutCopyMode = False

    Dim FC(1 to 28) As String

    PopulateFCValues() ' This is an imaginary sub that populates the FC Array

    Application.AddCustomList ListArray:=FC

    Dim sortNum As Long
    sortNum = Application.CustomListCount

    ActiveWorkbook.Worksheets("Pick List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Pick List").Sort.SortFields.Add Key:=Range("F2:F300000"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=sortNum, DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Pick List").Sort
        .SetRange Range("A1:V300000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

edited May 4, 2018 at 15:52

answered May 3, 2018 at 18:08

HackSlash

6,0272 gold badges28 silver badges58 bronze badges

3 Comments

Mathieu Guindon Over a year ago

Doesn't that make the custom list use "FCxx" string literals (or range addresses), rather than the variables OP means to use (which we don't know where they come from nor what they contain)?

HackSlash Over a year ago

I didn't consider that those could be variables. If they are, they should be an array. Dim FC(27) as String Then it could be passed directly to ListArray... That would replace kayRange my example.

Mathieu Guindon Over a year ago

That's quite literally what I did - replacing keyRange in your example. If you edit your answer to work with an array of variables I'll delete mine - yours was first, and is entirely correct save the part where OP clarifies (albeit in the comments section) that they're using 28 variables. Mine is just a rip-off with cosmetic tweaks =)

Collectives™ on Stack Overflow

Create a dynamic custom sort VBA Excel

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related