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