Se7enSquared
10/18/2019 - 11:38 PM

Sort table

Sorts a table by the given columns and keys

'----------------------------------------------------------------------------
' Sub: SortTable
' Params:  sheet_name:        String:     the sheet where the table is
'          tbl_name:          String:     the string for the list object name
'          sort_definition:   Dictionary: a dictionary object with keys for
'                                         column names and values of a or d
'                                         for asecnding or descending
' Requirements: VBE Menu: Tools->References->Microsoft Scripting Runtime
' Purpose: Sorts a table on the given columns in the given sort order
' Last Update: 12/1/2020
' Author: Gray
'----------------------------------------------------------------------------


Public Sub SortTable(ByVal sheet_name As String, _
                     ByVal tbl_name As String, _
                     ByVal sort_definition As Dictionary)
    
    Dim sort_col As Variant
    
    With Sheets(sheet_name).ListObjects(tbl_name).Sort
        .SortFields.Clear
        For Each sort_col In sort_definition.Keys()
            If sort_definition(sort_col) = "a" Then
                .SortFields.Add Key:=Range(tbl_name & "[" & sort_col & "]"), _
                SortOn:=xlSortOnValues, Order:=xlAscending
            Else
                .SortFields.Add Key:=Range(tbl_name & "[" & sort_col & "]"), _
                SortOn:=xlSortOnValues, Order:=xlDescending
            End If
        Next
        .Header = xlYes
        .Apply
    End With
End Sub