# 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:

```Copy```'###############################################################
'# 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):

```Copy```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:

```Copy```'###############################################################
'# 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):

```Copy```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``````