How to Sort Array with Excel VBA (Both Ascending and Descending Order)

Method 1 – Sort Array A-Z (In Ascending Order) in Excel VBA

Convert the selected range from an Excel worksheet into an array.

Dim MyArray As Variant

MyArray = Application.Transpose(Selection)

Sort the array by iterating through a for-loop.

For i = LBound(MyArray) To UBound(MyArray)

    For j = i + 1 To UBound(MyArray)

        If UCase(MyArray(i)) > UCase(MyArray(j)) Then

            Store = MyArray(j)

            MyArray(j) = MyArray(i)

            MyArray(i) = Store

        End If

    Next j

Next i

Insert the sorted array into the Excel worksheet by another for-loop.

For i = 1 To Selection.Rows.Count

    Selection.Cells(i, 1) = MyArray(i)

Next i

The complete VBA code will be:

⧭ VBA Code:

Sub Sort_Array_A_Z()

Dim MyArray As Variant
MyArray = Application.Transpose(Selection)

For i = LBound(MyArray) To UBound(MyArray)
    For j = i + 1 To UBound(MyArray)
        If UCase(MyArray(i)) > UCase(MyArray(j)) Then
            Store = MyArray(j)
            MyArray(j) = MyArray(i)
            MyArray(i) = Store
        End If
    Next j
Next i

For i = 1 To Selection.Rows.Count
    Selection.Cells(i, 1) = MyArray(i)
Next i

End Sub

VBA Code to Sort Array in Excel VBA

⧭ Output:

Select the range to be sorted (B4:B13 in this example) and run the following code.

The range will be sorted in ascending order.

Output Sort Array in Excel VBA

⧭ Note:

You can sort an array of numerical values too.

Method 2 – Sort Array Z-A (In Descending Order) in Excel VBA

The procedure is the same as that of ascending. In the code, use “Less than (<)” in place of the “Greater than (>)”.

The complete VBA code will be:

⧭ VBA Code:

Sub Sort_Array_Z_A()

Dim MyArray As Variant
MyArray = Application.Transpose(Selection)

For i = LBound(MyArray) To UBound(MyArray)
    For j = i + 1 To UBound(MyArray)
        If UCase(MyArray(i)) < UCase(MyArray(j)) Then
            Store = MyArray(j)
            MyArray(j) = MyArray(i)
            MyArray(i) = Store
        End If
    Next j
Next i

For i = 1 To Selection.Rows.Count
    Selection.Cells(i, 1) = MyArray(i)
Next i

End Sub

VBA Code Sort Array in Excel VBA