Excel 2010 Macro Sort Data
This function takes a mixed range of numeric and alpha characters and sorts them numerically first and then alphabetically. The result is placed in an array that can be displayed on a worksheet by using an array formula:
sorter(Rng)
The argument is Rng, the range to be sorted.
Function example:
Function sorter(Rng As Range) As Variant
‘returns an array
Dim arr1() As Variant
If Rng.Columns.Count > 1 Then Exit Function
arr1 = Application.Transpose(Rng)
QuickSort arr1
sorter = Application.Transpose(arr1)
End Function
The function uses two procedures to sort the data in the range:
Public Sub QuickSort(ByRef vntArr As Variant,
Optional ByVal lngLeft As Long = -2, _
Optional ByVal lngRight As Long = -2)
Dim i, j, lngMid As Long
Dim vntTestVal As Variant
If lngLeft = -2 Then lngLeft = LBound(vntArr)
If lngRight = -2 Then lngRight = UBound(vntArr)
If lngLeft < lngRight Then
lngMid = (lngLeft + lngRight) \ 2
vntTestVal = vntArr(lngMid)
i = lngLeft
j = lngRight
Do
Do While vntArr(i) < vntTestVal
i = i + 1
Loop
Do While vntArr(j) > vntTestVal
j = j - 1
Loop
If i <= j Then
Call SwapElements(vntArr, i, j)
i = i + 1
j = j - 1
End If
Loop Until i > j
If j <= lngMid Then
Call QuickSort(vntArr, lngLeft, j)
Call QuickSort(vntArr, i, lngRight)
Else
Call QuickSort(vntArr, i, lngRight)
Call QuickSort(vntArr, lngLeft, j)
End If
End If
End Sub
Private Sub SwapElements(ByRef vntItems As Variant,
ByVal lngItem1 As Long, _
ByVal lngItem2 As Long)
Dim vntTemp As Variant
vntTemp = vntItems(lngItem2)
vntItems(lngItem2) = vntItems(lngItem1)
vntItems(lngItem1) = vntTemp
End Sub