Sorting data using VBA: Shell Sort
2020-06-14 Sorting 0 289
ShellSort: Sorting large one-dimensional arrays:
ShellSort is very fast and is suitable for sorting large one-dimensional arrays.
Use ShellSort when less memory use is important, ShellSort is a bit slower than QuickSort.
ShellSort is not a stable sort method, you would usually not want to adapt this sort method for use with two-dimensional arrays, use MergeSort.
NOTE: The sorting algorithm in this document is adapted for use in VBA based on detailed descriptions found in Wikipedia.
Sub ShellSort(varArray As Variant, Optional blnCompareText As Boolean = False, Optional blnSortAscending As Boolean = True) ' updated 2015-08-01 by OPE ' sorts a one-dimensional array in ascending or descending order ' fast sorting (around 5% slower than QuickSort) but does not as use much stack space as QuickSort ' example: ShellSort varArrayVariable Dim lngCount As Long, OK As Boolean, lngGap As Long, i As Long, j As Long, varTemp As Variant If Not IsArray(varArray) Then Exit Sub ' no array lngCount = UBound(varArray, 1) - LBound(varArray, 1) + 1 lngGap = lngCount Do While lngGap > 1 lngGap = lngGap \ 2 Do OK = True For i = 1 To lngCount - lngGap j = i + lngGap If blnCompareText Then If blnSortAscending Then If StrComp(varArray(j), varArray(i), vbTextCompare) < 0 Then varTemp = varArray(j) varArray(j) = varArray(i) varArray(i) = varTemp OK = False End If Else If StrComp(varArray(j), varArray(i), vbTextCompare) > 0 Then varTemp = varArray(j) varArray(j) = varArray(i) varArray(i) = varTemp OK = False End If End If Else If blnSortAscending Then If varArray(j) < varArray(i) Then varTemp = varArray(j) varArray(j) = varArray(i) varArray(i) = varTemp OK = False End If Else If varArray(j) > varArray(i) Then varTemp = varArray(j) varArray(j) = varArray(i) varArray(i) = varTemp OK = False End If End If End If Next i Loop Until OK Loop 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.