Sorting data using VBA: Range Sort

 2020-06-14    Sorting    0   

Sorting a worksheet cell 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 cell 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 cell range containing with a header row:

Function RangeSort(objRange As Range, Optional lngSortColumn As Long = 1, Optional lngSortOrder As XlSortOrder = xlAscending, _
    Optional blnHasHeader As Boolean = True, Optional blnMatchCase As Boolean = False) As Boolean
' updated 2012-09-04 by OPE
' sorts objRange in ascending or descending order on column lngSortColumn 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
' objRange must be a single area, preferably including a header row
' lngSortColumn must be an integer number >= 1 and <= objRange.ColumnsCount
' function returns True if the range 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: RangeSort Range("A1:D1000"), 1 ' sort by column A in ascending order
' example: RangeSort Range("A1:D1000"), 3, xlDescending ' sort by column C in descending order
' example: RangeSort Range("A1:D1000"), 2, xlAscending ' sort by column B in ascending order
Dim objSortKey As Range, r As Long, blnASU As Boolean, lngCursor As XlMousePointer
    RangeSort = False
    If objRange Is Nothing Then Exit Function
    
    With objRange
        If .Areas.Count > 1 Then Exit Function
        If lngSortColumn < 1 Or lngSortColumn > .Columns.Count Then Exit Function
        
        RangeSort = True
        r = .Rows.Count
        If blnHasHeader Then
            If r < 3 Then Exit Function ' nothing to sort
            
            Set objSortKey = .Range(Cells(2, lngSortColumn), Cells(r, lngSortColumn))
        Else
            If r < 2 Then Exit Function ' nothing to sort
            
            Set objSortKey = .Columns(lngSortColumn)
        End If
    End With
    
    ' this is useful when sorting very large ranges
    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 objRange.Parent.Sort
        .SortFields.Clear
        .SortFields.Add Key:=objSortKey, SortOn:=xlSortOnValues, Order:=lngSortOrder, DataOption:=xlSortNormal
        .SetRange objRange
        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:
    RangeSort = False
    Resume Next
End Function
Here are a few examples on how to use the macro above:

Sub SortingDataInCellRange_Example1()
' updated 2020-06-10 by OPE
' sort data in a cell range, read the result into an array variable
Dim varItems As Variant
    ' sort the cell range content (included a header row) in the desired order
    RangeSort Range("A1:D1000"), 1
    ' read the sorted range content into an array (included the header row)
    varItems = Range("A1:D1000").Value
    ' read the sorted range content into an array (excluded the header row)
    'varItems = Range("A2:D1000").Value
    ' do something with the array items
    Array_ReplaceErrors varItems
End Sub

Sub SortingDataInCellRange_Example2()
' updated 2020-06-10 by OPE
' sort data in a cell range, read the result into an array variable, restore the original content in the sorted cell range
Dim objRange As Range, varItems As Variant
    ' define the cell range you want to sort (included a header row)
    Set objRange = Range("A1:D1000")
    ' sort the range content in the desired input order
    RangeSort objRange, 1
    ' read the sorted range content into an array (included the header row)
    varItems = objRange.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 = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count).Value
    ' sort the range content back into the desired display order
    RangeSort objRange, 3
    ' do something with the array items
    Array_ReplaceErrors varItems
End Sub

Sub SortingDataInCellRange_Example3()
' updated 2020-06-10 by OPE
' sort data in a cell range, read the result into an array variable, restore the original content in the sorted cell range
Dim objRange As Range, varCurrent As Variant, varItems As Variant
    ' define the cell range you want to sort (included a header row)
    Set objRange = Range("A1:D1000")
    ' read the current range content into an array variable
    varCurrent = objRange.Formula
    ' sort the range content in the desired input order
    RangeSort objRange, 1
    ' read the range content into an array (included the header row)
    varItems = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count).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 = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count).Value
    ' restore the original range content back into the original sort order
    objRange.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.