Sorting data using VBA: Table Sort
2020-06-14 Sorting 0 264
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 FunctionHere 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.