VBA Tip: Sort an Array

Here's a ready-to-use solution to sort an Array in VBA without modifying the cells.

This solution is designed for a 1-dimensional array and ascending (alphabetical) sorting.

A second solution to sort a 1-dimensional array in descending (reverse alphabetical) order is available below.

Ascending Sort

First, copy this procedure into a module:

'Ascending (alphabetical) sort of a 1-dimensional array
'Source: https://www.excel-pratique.com/en/vba_tricks/array-sort-function
Sub AscendingSort(myArray)
    num = UBound(myArray)
    tempArray = myArray
    Erase myArray
    For i = 0 To num
        position = 0
        For l = 0 To num
            If LCase(tempArray(i)) > LCase(tempArray(l)) And i <> l Then
                position = position + 1
            End If
        Next
        For j = 1 To 1
            If myArray(position) = "" Then
                myArray(position) = tempArray(i)
            Else
                position = position + 1
                j = j - 1
            End If
        Next
    Next
End Sub

Then simply add the line AscendingSort myArray to sort your array:

Sub example()
    
    Dim myArray(29)
    
    'For example: copy from A1 to A30 into an array
    For i = 1 To 30
        myArray(i - 1) = Range("A" & i)
    Next

    'Sort the array
    AscendingSort myArray
    
    'For example: copy the sorted array into column B
    For i = 1 To 30
        Range("B" & i) = myArray(i - 1)
    Next

End Sub

Descending Sort

Copy this procedure into a module:

'Descending (reverse alphabetical) sort of a 1-dimensional array
'Source: https://www.excel-pratique.com/en/vba_tricks/array-sort-function
Sub DescendingSort(myArray)
    num = UBound(myArray)
    tempArray = myArray
    Erase myArray
    For i = 0 To num
        position = 0
        For l = 0 To num
            If LCase(tempArray(i)) > LCase(tempArray(l)) And i <> l Then
                position = position + 1
            End If
        Next
        For j = 1 To 1
            If myArray(num - position) = "" Then
                myArray(num - position) = tempArray(i)
            Else
                position = position + 1
                j = j - 1
            End If
        Next
    Next
End Sub

Then add the line DescendingSort myArray in your code to sort the array:

Sub example()
    
    Dim myArray(29)
    
    'For example: copy from A1 to A30 into an array
    For i = 1 To 30
        myArray(i - 1) = Range("A" & i)
    Next

    'Sort the array
    DescendingSort myArray
    
    'For example: copy the sorted array into column B
    For i = 1 To 30
        Range("B" & i) = myArray(i - 1)
    Next

End Sub