Sorting data using VBA: Table Sort

 2020-06-14    Sorting    0   

Sorting a worksheet table range:
The built-in sort method in Excel is usually a very fast solution, and it is a stable sort method, something that might be important when you are sorting data with multiple columns.
When you use a stable sort method items with equal sort keys are returned in the same order in the sorted result as they had before being sorted.
Sorting data in a table range usually only works in an unprotected worksheet and might change the sort order applied by the user.
Your code can implement steps to unprotect a worksheet before sorting, and then re-protecting the worksheet after sorting.
Your code can also implement steps to restore the range content after sorting it and reading the sorted result into an array variable.
The macro below can be used to sort data in a defined table range with a header row:

Function TableSort(objListObject As ListObject, Optional strColumnCaption As String = vbNullString, Optional lngSortOrder As XlSortOrder = xlAscending, _
    Optional blnHasHeader As Boolean = True, Optional blnMatchCase As Boolean = False) As Boolean
' updated 2012-09-04 by OPE
' sorts objListObject in ascending or descending order on column strColumnCaption using the built-in sort in Excel
' very fast and stable sort method, items with equal sort keys are returned in the same order in the sorted result as they had before being sorted
' objListObject must be a defined table range, preferably including a header row
' strColumnCaption must be a string representing the table column name to sort on
' function returns True if the table was successfully sorted or didn't need to be sorted
' if you need to sort on multiple columns, run this function multiple times and sort by columns in reverse order, sorting the most important column last
' example: TableSort ActiveSheet.ListObjects(1), "FieldName1" ' sort by "FieldName1" in ascending order
' example: TableSort ActiveSheet.ListObjects(1), "FieldName2", xlDescending ' sort by "FieldName2" in descending order
' example: TableSort ActiveSheet.ListObjects(1), "FieldName3", xlAscending ' sort by "FieldName3" in ascending order
Dim strKey As String, r As Long, blnASU As Boolean, lngCursor As XlMousePointer
    TableSort = False
    If objListObject Is Nothing Then Exit Function
    If Len(strColumnCaption) = 0 Then Exit Function
    
    TableSort = True
    With objListObject
        r = .DataBodyRange.Rows.Count
        If blnHasHeader Then
            If r < 3 Then Exit Function ' nothing to sort
        Else
            If r < 2 Then Exit Function ' nothing to sort
        End If
        strKey = .Name & "[[#All],[" & strColumnCaption & "]]"
    End With
    
    TableSort = False
    ' this is useful when sorting very large tables
    blnASU = Application.ScreenUpdating
    If blnASU Then Application.ScreenUpdating = False ' turn off screen updating
    lngCursor = Application.Cursor ' change the cursor
    If lngCursor <> xlWait Then Application.Cursor = xlWait
    
    On Error GoTo ErrorHandler
    With objListObject.Sort
        .SortFields.Clear
        .SortFields.Add2 Range(strKey), xlSortOnValues, lngSortOrder, , xlSortNormal
        If blnHasHeader Then .Header = xlYes Else .Header = xlNo
        .MatchCase = blnMatchCase
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    If lngCursor <> xlWait Then Application.Cursor = lngCursor ' restore the cursor
    If blnASU Then Application.ScreenUpdating = True ' restore screen updating
    Exit Function

ErrorHandler:
    TableSort = False
    Resume Next
End Function
Here are a few examples on how to use the macro above:

Sub SortingDataInTableRange_Example1()
' updated 2020-06-10 by OPE
' sort data in a table range, read the result into an array variable
Dim varItems As Variant
    ' sort the table content (included a header row) in the desired order
    Table_Sort ActiveSheet.ListObjects("MyTableName"), "ID"
    ' read the sorted table content into an array (included the header row)
    varItems = ActiveSheet.ListObjects("MyTableName").Range.Value
    ' read the sorted table content into an array (excluded the header row), will not return an array if the range is a single cell only
    'varItems = ActiveSheet.ListObjects("MyTableName").DataBodyRange.Value
    ' do something with the array items
    Array_ReplaceErrors varItems
End Sub

Sub SortingDataInTableRange_Example2()
' updated 2020-06-10 by OPE
' sort data in a table range, read the result into an array variable, restore the original content in the sorted table range
Dim objTable As ListObject, varItems As Variant
    ' define the table range you want to sort (included a header row)
    Set objTable = ActiveSheet.ListObjects("MyTableName")
    ' sort the table content in the desired input order
    Table_Sort objTable, "ID"
    ' read the sorted table content into an array (included the header row)
    varItems = objTable.Range.Value
    ' read the sorted table content into an array (excluded the header row), will not return an array if the range has one row and one column
    'varItems = objTable.DataBodyRange.Value
    ' sort the range content back into the desired display order
    Table_Sort objTable, "Value"
    ' do something with the array items
    Array_ReplaceErrors varItems
End Sub

Sub SortingDataInTableRange_Example3()
' updated 2020-06-10 by OPE
' sort data in a table range, read the result into an array variable, restore the original content in the sorted table range
Dim objTable As ListObject, varCurrent As Variant, varItems As Variant
    ' define the table range you want to sort (included a header row)
    Set objTable = ActiveSheet.ListObjects("MyTableName")
    ' read the current range content into an array variable
    varCurrent = objTable.Range.Formula
    ' sort the range content in the desired input order
    Table_Sort objTable, "ID"
    ' read the range content into an array (included the header row)
    varItems = objTable.Range.Value
    ' read the sorted range content into an array (excluded the header row), will not return an array if the range has one row and one column
    'varItems = objTable.DataBodyRange.Value
    ' restore the original range content back into the original sort order
    objTable.Range.Formula = varCurrent
    ' do something with the array items
    Array_ReplaceErrors varItems
End Sub

Here are some of the options you can use to sort data in Excel using VBA: Stable sort methods:
RangeSort, TableSort, BubbleSort and MergeSort are stable sort methods:
items with equal sort keys are returned in the same order in the sorted result as they had before being sorted.

QuickSort and ShellSort are not stable sort methods and you would normally not want to use these with two-dimensional arrays.

See this post if you need information about how to read from and write to worksheet cells using array variables (including code for the Array_ReplaceErrors macro).


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.