Sorting data using VBA: Table Sort
2020-06-14 Sorting 0 645
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:
- Sorting two-dimensional arrays: SORT worksheet function
- Sorting a worksheet range: RangeSort
- Sorting a worksheet table: TableSort
- Sorting small arrays (<= 1000 items): BubbleSort
- Sorting a large one-dimensional array (> 1000 items): QuickSort (might use a lot of memory)
- Sorting a large one-dimensional array (> 1000 items): ShellSort (uses less memory than QuickSort)
- Sorting a large two-dimensional array (> 1000 items): MergeSort
RangeSort, TableSort, BubbleSort, MergeSort and the SORT worksheet function 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.