Trying to sort a ListObject Table using a custom order range

[rtemen]

Hi.
I have a sheet with a table. I am trying to sort table 'DeviceSummary' using Column 'ID'

Table DeviceSummary:
ID Col2 Col3 Value
1,1 A B 12
1,2 C D 15

etc

I need to sort the table using column ID.
I set up a range defining the criteria to sort on.

Here is my code: (This is pulled from a forum where the user thanked the people that gave this to him saying that it works great.)
I have tweaked things til I am going nuts, and cannot get anywhere.

    ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary").Sort.SortFields.Clear
    
    On Error Resume Next
    Application.AddCustomList listArray:=Worksheets("Rates").Range("CustomList")  ' My list of sorting instructions
    On Error GoTo 0
    
    
    With ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary").Sort
        .SortFields.Clear
        .SortFields.Add _
            key:=.ListColumns(ID), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=Worksheets("Rates").Range("CustomList"), _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

No matter what I have tried, I keep getting a Run-Time error '438': Object doesn't support this property or method.

Any help is appreciated.
Rich

 

[Norie]

Rich

Where do you get the error?

Also, have you checked the custom list has been created/added?

 

[rtemen]

Rich

Where do you get the error?

Also, have you checked the custom list has been created/added?

I apparently do not know how to respond properly on this forum.
Hope this is right.

The error comes on the line .sortfields.add.
Yes, the custom list is there and works.

I first set up a search using the custom list on a test workbook only using it on data range in a sheet.

Now, I am trying to implement it on this workbook where the data is actually in a table, hence the ListObject stuff.
But I cannot seem to get all the parts right.

Rich

 

[Norie]

Rich

At first look I think this is the problem.

Key:=.ListColumns(ID)

.ListColumns is not a property of the Sort object of a ListObject, in this case not a property of ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary").Sort.

What are you trying to set the Key to?

 

[RoryA]

Assuming the custom list is a columnar range, you could use this without adding the custom list:

    Dim lo as listobject
set lo = ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary")
    
    
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add _
            key:=lo.ListColumns(ID), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=Join(application.Transpose(Worksheets("Rates").Range("CustomList")), ","), _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[rtemen]

Assuming the custom list is a columnar range, you could use this without adding the custom list:

    Dim lo as listobject
set lo = ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary")
    
    
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add _
            key:=lo.ListColumns(ID), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=Join(application.Transpose(Worksheets("Rates").Range("CustomList")), ","), _
            DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

The SortFields.add line gives me a Run-time error 13: Type Mismatch.

Also, the column that makes up my Custom List is a column of strings. The strings contain a comma.
Like so:
1,1
1,2
1,3
1,4
1,5
etc.

So we cannot build the list using your Join.
Interestingly, this function was working on my other spreadsheet where I just had a range of cells, rather than an actual table.
But, on this workbook, I cannot get this to work on the Table.

Rich

 

[Norie]

Rich

The problem with the code in your original post was this.

key:=.ListColumns(ID)

So what are you trying to use for the Key argument?

If it's a column within the table you'll need to fully qualify ListColumns and use Range.

So, stealing a little from Rory's code, try this.

Dim lo As ListObject

    Set lo = ActiveWorkbook.Worksheets("Summary").ListObjects("DeviceSummary")

    On Error Resume Next
    Application.AddCustomList listArray:=Worksheets("Rates").Range("CustomList")  ' My list of sorting instructions
    On Error GoTo 0

    With lo.Sort
        .SortFields.Clear
        .SortFields.Add _
                Key:=lo.ListColumns(ID).Range, _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                CustomOrder:=Application.CustomListCount, _
                DataOption:=xlSortNormal
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With