Best code to sort a table column in a VBA macro

[JenniferMurphy]

I am working on a macro that will do some processing on the data in a table. When it is done, it might want to sort the table on one column. Having no idea how to do that, I recorded 3 macros with different portions of the table column selected.

This was my first try. I selected one cell in the column.

Sub Macro1()

' Recorded with one cell in column selected

    Range("D15").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("D15:D22"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This was my second try. I selected the column header.

Sub Macro2()

' Recorded with column header selected

    Range("TblExample[[#Headers],[WtdRtg]]").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("TblExample[[#Headers],[#Data],[WtdRtg]]"), SortOn:= _
        xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

This was my third try. I selected the entire column, but not the header. This looks like the best option. But do I need all of that code?

Sub Macro3()

' Recorded with entire column (without header) selected

    Range("TblExample[WtdRtg]").Select
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort.SortFields. _
        Add2 Key:=Range("TblExample[WtdRtg]"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Example").ListObjects("TblExample").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks for any help.

 

[JenniferMurphy]

You need lotable.sort in the with statement, not just lotable

Thank you