VBA Tip: Sort an array

Here's a ready-to-use solution for sorting an array in VBA without using cells.

This solution is intended for a one-dimensional array and an ascending sort (alphabetical order).

A second solution for sorting a one-dimensional array in descending order (reverse alphabetical order) can be found below.

Ascending sort

If your array is named "my_array", simply add the following code to sort this array:

'###############################################################
'# Start of the (1 dimensional) array sorting named "my_array" #
'# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
'###############################################################
nb = UBound(my_array)
temp_array = my_array
Erase my_array
For i = 0 To nb
    pos = 0
    For l = 0 To nb
        If LCase(temp_array(i)) > LCase(temp_array(l)) And i <> l Then
            pos = pos + 1
        End If
    Next
    For ii = 1 To 1
        If my_array(pos) = "" Then
            my_array(pos) = temp_array(i)
        Else
            pos = pos + 1
            ii = ii - 1
        End If
    Next
Next
'###############################################################
'# End of the (1 dimensional) array sorting named "my_array"   #
'# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
'###############################################################

Here's a simple example of how this sort code is used ("my_array" receives 30 values, "my_array" is sorted, and the "my_array" data are entered in the B column):

Sub ascending_sort()
    
    Dim my_array(29)
    
    'For example: copy A1 to A30 in an array
    For l = 1 To 30
        my_array(l - 1) = Range("A" & l)
    Next

    '###############################################################
    '# Start of the (1 dimensional) array sorting named "my_array" #
    '# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
    '###############################################################
    nb = UBound(my_array)
    temp_array = my_array
    Erase my_array
    For i = 0 To nb
        pos = 0
        For l = 0 To nb
            If LCase(temp_array(i)) > LCase(temp_array(l)) And i <> l Then
                pos = pos + 1
            End If
        Next
        For ii = 1 To 1
            If my_array(pos) = "" Then
                my_array(pos) = temp_array(i)
            Else
                pos = pos + 1
                ii = ii - 1
            End If
        Next
    Next
    '###############################################################
    '# End of the (1 dimensional) array sorting named "my_array"   #
    '# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
    '###############################################################

    'For example: copy of the sorted array in the B column
    For l = 1 To 30
        Range("B" & l) = my_array(l - 1)
    Next

End Sub

Descending sort

If your array is named "my_array", simply add the following code to sort this array:

'###############################################################
'# Start of the (1 dimensional) array sorting named "my_array" #
'# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
'###############################################################
nb = UBound(my_array)
temp_array = my_array
Erase my_array
For i = 0 To nb
    pos = 0
    For l = 0 To nb
        If LCase(temp_array(i)) > LCase(temp_array(l)) And i <> l Then
            pos = pos + 1
        End If
    Next
    For ii = 1 To 1
        If my_array(nb - pos) = "" Then
            my_array(nb - pos) = temp_array(i)
        Else
            pos = pos + 1
            ii = ii - 1
        End If
    Next
Next
'###############################################################
'# End of the (1 dimensional) array sorting named "my_array"   #
'# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
'###############################################################

Here's a simple example of how this sort code is used ("my_array" receives 30 values, "my_array" is sorted, and the "my_array" data are entered in the B column):

Sub descending_sort()
    
    Dim my_array(29)
    
    'For example: copy A1 to A30 in an array
    For l = 1 To 30
        my_array(l - 1) = Range("A" & l)
    Next

    '###############################################################
    '# Start of the (1 dimensional) array sorting named "my_array" #
    '# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
    '###############################################################
    nb = UBound(my_array)
    temp_array = my_array
    Erase my_array
    For i = 0 To nb
        pos = 0
        For l = 0 To nb
            If LCase(temp_array(i)) > LCase(temp_array(l)) And i <> l Then
                pos = pos + 1
            End If
        Next
        For ii = 1 To 1
            If my_array(nb - pos) = "" Then
                my_array(nb - pos) = temp_array(i)
            Else
                pos = pos + 1
                ii = ii - 1
            End If
        Next
    Next
    '###############################################################
    '# End of the (1 dimensional) array sorting named "my_array"   #
    '# www.excel-pratique.com/en/vba_tricks/array-sort-function    #
    '###############################################################

    'For example: copy of the sorted array in the B column
    For l = 1 To 30
        Range("B" & l) = my_array(l - 1)
    Next

End Sub