Excel - Add Custom Sort List in VBA

Question

I have some code which first refreshes a set of data from ODBC, then refreshes several pivots linked to that data.
The pivots need to have a custom sort order in order to show the data in the "correct" order to the customer.

The sort order needs to be defined by the customer and can change at any point. To enable this, I have added a two tables containing the names of the groups and account codes on a settings worksheet. They can then order the rows in these tables into the order they wish.
I then need to add the data from these tables into a sort list that can be set on the pivots.

I am using Application.AddCustomList to add the custom list into Excel. This works fine if I just try to add one list, but when it try to add the second, it errors with:

"Method 'AddCustomList' of object '_Application' failed".

Below is the code:

    ' Deletes old sort orders
        On Error Resume Next
        Dim i As Long
        For i = 1 To Application.CustomListCount
           Application.DeleteCustomList (i)
        Next i

        On Error GoTo 0

    ' Adds custom sort for customers to appplication
        Application.AddCustomList ListArray:=Range("tblSortList[Account Code]")

    ' Gets the number of the custom sorts
        CustomerSortNo = Application.CustomListCount


    ' Adds custom sort for groups to appplication
         Application.AddCustomList ListArray:=Range("tblGroupSort[Group]")


    ' Gets the number of the categorysort
        CategorySortNo = Application.CustomListCount

    ' Sorts the pivots
        Pt.SortUsingCustomLists = True
        PTTotals.SortUsingCustomLists = True
        PTYearGroup.SortUsingCustomLists = True
        PTGroupAvg.SortUsingCustomLists = True


        Pt.PivotFields("Account Code").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CustomerSortNo + 1  ' +1 as No Calcualtion is number 1.
        PTTotals.PivotFields("Account Code").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CustomerSortNo + 1  ' +1 as No Calcualtion is number 1


        Pt.PivotFields("Group").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CategorySortNo + 1  ' +1 as No Calcualtion is number 1.
        PTTotals.PivotFields("Group").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CategorySortNo + 1  ' +1 as No Calcualtion is number 1
        PTYearGroup.PivotFields("Group").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CategorySortNo + 1  ' +1 as No Calcualtion is number 1
        PTGroupAvg.PivotFields("Group").DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=CategorySortNo + 1  ' +1 as No Calcualtion is number 1

Current it will fail on the line: Application.AddCustomList ListArray:=Range("tblGroupSort[Group]")

If I re-order the code and add the Group sort list first, it will run fine and error on the Account Code list.
I have tried separating out the adding of the custom list into a sub for each, but it made no difference.

How can I add two range lists?

Asger · Accepted Answer · 2019-06-01 06:22:17Z

1

Your code to assign and use the custom lists works, but the loop deleting them, not.
You have to step the list downwards to catch each element (numbering changes, if you deleted one):

For i = Application.CustomListCount To 1 Step -1
   Application.DeleteCustomList (i)
Next i

edited Jun 1, 2019 at 6:22

answered May 31, 2019 at 9:33

Asger

3,8773 gold badges14 silver badges39 bronze badges

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

1 Comment

SQLSam Over a year ago

Thank you. Having stepped through the running code - you were right - the error was occurring as it was trying to add a sort that already existed, as it hadn't previously deleted it as I had expected.

Collectives™ on Stack Overflow

Excel - Add Custom Sort List in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related