Sorting data using VBA: Using the SORT worksheet function
2023-06-04 Sorting 0 295
Sorting data using VBA: Using the SORT worksheet function
The SORT worksheet function: Sorting two-dimensional arrays:
The SORT worksheet function is suitable for sorting two-dimensional arrays very fast.
The SORT worksheet function is 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.
The SORT worksheet function requires a range/array without a header row, the procedure below supports arrays that has a header row.
The procedure below will return the sorted array in the same order as the worksheet sort functionality, some sort methods might sort text in a different order depending on the country settings.
Sub Array_Sort(varArray As Variant, varSortCol As Variant, Optional blnHasHeaderRow As Boolean = False, Optional blnSortAscending As Boolean = True) ' updated 2023-06-03 by OPE ' varArray must be a two-dimensional array with or without header row, use the .Value2 property when population the array from a worksheet range with date values ' varSortCol can be a single column number a one-dimensional array with column numbers ' if blnHasHeaderRow = True then varSortCol can be a single column number or caption or a one-dimensional array with column numbers or captions ' uses the SORT worksheet function to sort the array content ' example use: ' Array_Sort varArray, "Amount", True ' sort array with a header row in ascending order based on content in column Amount ' Array_Sort varArray, "Amount", True, False ' sort array with a header row in descending order based on content in column Amount ' Array_Sort varArray, Array("Company", "Department", "Fullname"), True ' sort array with a header row in ascending order based on content in columns Company, Department, Fullname ' Array_Sort varArray, Array(4, 5, "Fullname"), True ' sort array with a header row in ascending order based on content in columns 4, 5, Fullname ' ' Array_Sort varArray, 1, False ' sort array without a header row in ascending order based on content in column 1 ' Array_Sort varArray, 4, False, False ' sort array without a header row in descending order based on content in column 4 ' Array_Sort varArray, Array(4, 5, 1), False ' sort array without a header row in ascending order based on content in column 4, 5 and 1 If Not IsArray(varArray) Then Exit Sub ' not an array Dim r As Long, c As Long On Error Resume Next r = UBound(varArray, 1) - LBound(varArray, 1) + 1 c = UBound(varArray, 2) - LBound(varArray, 2) + 1 On Error GoTo 0 If c < 1 Then Exit Sub ' not a two-dimensional array Dim varCI As Variant, i As Long If blnHasHeaderRow Then If r < 3 Then Exit Sub ' nothing to sort in array with a header row Dim varItem As Variant, blnFoundColumn As Boolean varCI = varSortCol If Not IsArray(varCI) Then varCI = Array(varCI) ' convert column header captions to column numbers r = LBound(varArray, 1) i = -1 For Each varItem In varCI i = i + 1 blnFoundColumn = False On Error Resume Next For c = LBound(varArray, 2) To UBound(varArray, 2) If StrComp(Trim(varItem), Trim(varArray(r, c)), vbTextCompare) = 0 Then varCI(i) = c blnFoundColumn = True Exit For End If Next c If Not blnFoundColumn Then If IsNumeric(varItem) Then On Error Resume Next varCI(i) = CLng(varItem) On Error GoTo 0 End If End If Next varItem Dim varBody() As Variant ReDim varBody(LBound(varArray, 1) To UBound(varArray, 1) - 1, LBound(varArray, 2) To UBound(varArray, 2)) For r = LBound(varArray, 1) + 1 To UBound(varArray, 1) ' skip the header row For c = LBound(varArray, 2) To UBound(varArray, 2) varBody(r - 1, c) = varArray(r, c) Next c Next r Array_Sort varBody, varCI, False, blnSortAscending ' returns varBody as a two-dimensional array with lower bound = 1 Dim j As Long i = LBound(varArray, 1) ' header row For r = LBound(varBody, 1) To UBound(varBody, 1) ' no header row i = i + 1 j = LBound(varArray, 2) - 1 For c = LBound(varBody, 2) To UBound(varBody, 2) j = j + 1 varArray(i, j) = varBody(r, c) Next c Next r Exit Sub ' finished sorting array with header End If If r < 2 Then Exit Sub ' nothing to sort in array without a header row Dim lngSortOrder As Long lngSortOrder = 1 ' sort ascending If Not blnSortAscending Then lngSortOrder = -1 ' sort descending varCI = varSortCol If Not IsArray(varCI) Then varCI = Array(varCI) For i = UBound(varCI) To LBound(varCI) Step -1 c = LBound(varArray, 2) - 1 On Error Resume Next c = CLng(varCI(i)) If c >= LBound(varArray, 2) And c <= UBound(varArray, 2) Then varArray = Application.WorksheetFunction.Sort(varArray, c, lngSortOrder) ' returns varArray as a two-dimensional array with lower bound = 1 End If On Error GoTo 0 Next i 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.