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: Stable sort methods:
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.