Sorting data using VBA: Range Sort
2020-06-14 Sorting 0 178
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 Range_Sort(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-06 by OPE ' sorts objRange in ascending or descending order on column lngSortColumn using the built-in sort in Excel ' very quick 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: Range_Sort Range("A1:D1000"), 1 ' sort by column A in ascending order ' example: Range_Sort Range("A1:D1000"), 3, xlDescending ' sort by column C in descending order ' example: Range_Sort 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 Range_Sort = 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 Range_Sort = True r = .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 Set objSortKey = .Columns(lngSortColumn) If blnHasHeader Then Set objSortKey = objSortKey.Offset(1, 0) Set objSortKey = objSortKey.Resize(objSortKey.Rows.Count - 1, 1) 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: Range_Sort = False Resume Next End FunctionHere 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:
- 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.