VBA: Code for ListObject Table Not Sorting

Question

Hello!

I have a problem. My VBA code for sorting a regular table is not working for some reason.

I am using Column D as a Lookup column, so the column must be sorted correctly. I believe Lookup requires an "ascending" order to function properly.

Here is a sample of the table I am attemping to sort:

Tag Name	Description	Specifier	Concatenated Lookup Text
S841_Tag	Desc1	Spec1	S841WARNALM_SEAL[1].4
S840_Tag	Desc2	Spec2	S840WARNALM_SEAL[18].6
S303_Tag	Desc3	Spec3	S303WARNALM_SEAL[30].27
S202_Tag	Desc4	Spec4	S202WARNALM_SEAL[33].22
S303_Tag	Desc5	Spec5	S303WARNALM_SEAL[30].14

The data is currently in a normal Table (called "WARNTable"). If I manually sort Column D "A to Z", the table sorts as expected, and the LOOKUP function works. If I run the following code, the table doesn't sort at all:

Sub SortTable_WARNTanble

Dim ws As Worksheet

Set ws = Worksheets("WARN Tags")

    ActiveWorkbook.ws.ListObjects("WARNTable").Sort.SortFields. _

        Clear

    ActiveWorkbook.ws.ListObjects("WARNTable").Sort.SortFields. _

        Add Key:=Range("WARNTable[[#All],[Concatenated Lookup Text]]"), SortOn:= _

        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With ActiveWorkbook.ws.ListObjects("WARNTable").Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

Any ideas?

Thanks,

Parker

Answer 1

Hi,

Try this. Note the bold part is the header from column 4 so change if necessary.

Sub SortTable_WARNTable()

Dim WARNTable As Excel.ListObject

Set WARNTable = ActiveWorkbook.Worksheets("WARN Tags").ListObjects("WARNTable")

With WARNTable

.Sort.SortFields.Clear

    .Sort.SortFields.Add _

        Key:=Range("WARNTable[Concatenated Lookup Text]"), SortOn:=xlSortOnValues, Order:=xlAscending, _

        DataOption:=xlSortNormal

    With .Sort

        .Header = xlYes

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End With

End Sub