CODEWITHSUNDEEP
vbaexcel
viper941
viper941

Reputation: 156

Create a dynamic custom sort VBA Excel

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.

Upvotes: 1

Views: 1223

Answers (2)

HackSlash
HackSlash

Reputation: 5803

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

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

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

Upvotes: 1

Related Questions