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.

 

[kevin9999]

Thanks Jennifer, and Doh! - typo on the table header name in my code!

.SortFields.Add Key:=Worksheets("Example").Range("TblExample[WtgRtg]"), Order:=2

Please try the amended code below:

Sub JMurph2()
    With Worksheets("Example").ListObjects("TblExample").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Worksheets("Example").Range("TblExample[WtdRtg]"), Order:=2
        .Header = xlYes
        .Apply
    End With
End Sub

 

[JenniferMurphy]

Thanks Jennifer, and Doh! - typo on the table header name in my code!

Double Doh! I should have cought that myself. Now it works. Thanks.

 

[kevin9999]

Thanks Jennifer - although at least you've got an excuse - I think it's close to 2:00 am where you are

 

[JenniferMurphy]

Thanks Jennifer - although at least you've got an excuse - I think it's close to 2:00 am where you are

2:01

 

[RoryA]

FWIW, since you refer to the same table twice in the code, I'd suggest assigning it to a variable so you only need to change it in one place if/when needed.

 

[kevin9999]

FWIW, since you refer to the same table twice in the code, I'd suggest assigning it to a variable so you only need to change it in one place if/when needed.

Good point Rory!

 

[JenniferMurphy]

FWIW, since you refer to the same table twice in the code, I'd suggest assigning it to a variable so you only need to change it in one place if/when needed.

What type of variable? Can you post the assignment code?

 

[RoryA]

A ListObject:

Sub JMurph2()
dim lo as listobject: set lo = Worksheets("Example").ListObjects("TblExample")
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add Key:=lo.listcolumns("WtdRtg").Range, Order:=2
        .Header = xlYes
        .Apply
    End With
End Sub

 

[JenniferMurphy]

A ListObject:

Sub JMurph2()
dim lo as listobject: set lo = Worksheets("Example").ListObjects("TblExample")
    With lo.Sort
        .SortFields.Clear
        .SortFields.Add Key:=lo.listcolumns("WtdRtg").Range, Order:=2
        .Header = xlYes
        .Apply
    End With
End Sub

I tried to adapt your code to make use of my variable names, but I've done something wrong. My code is getting this error.

Here's my code. The error is on the .sortfields statement at the bottom.

' Named range and derivative variables
Const rnDataTableName As String = "TableName"   'Cell containing the name of the table
  Dim rnTable As String                           'The name of the table
  Dim loTable As ListObject                       'The table as a list object

 . . .
 
' Call my IsATable UDF to check that the name is for a table
If Not IsATable(rnTable) Then Exit Function

' Define a ListObject variable. It will replace ActiveSheet.ListObject in the rest of the code
  On Error GoTo BadTableName      'Handle bad table names
  Set loTable = Range(rnTable).ListObject 'Load the list object
  On Error GoTo 0                         'Resume normal error handling
  GoTo TableNameOK                        'Resume processing
BadTableName:
  ErrMsg "Invalid table name (" & rnTable & ")": Exit Function:
TableNameOK:

' Load the table headers into one array and the body into another
aTblHdr = loTable.HeaderRowRange.Value2  'Load the table header row
aTblDat = loTable.DataBodyRange.Value2   'Load the table body

 . . .
 
' Write out just the weighted ratings column (2) of the array
loTable.ListColumns(aTblHdr(1, iWtdRtgCol)).DataBodyRange.Resize(UBound(aTblDat, 1)) _
       = Application.index(aTblDat, 0, iWtdRtgCol)

'Sort the table on the WtdRtg column
With loTable
  .SortFields.Clear   'This statement gets the error message
  .SortFields.Add Key:=loTable.ListColumns(sHdrWtdRtg).Range, Order:=2
  .Header = xlYes
  .Apply
End With

What am I doing wrong?

Thanks

 

[RoryA]

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