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

In this article, I’ll show you how you can sort an array with VBA in Excel. You’ll learn to sort the array both A-Z and Z-A with proper examples and illustrations.

Sort Array in Excel VBA (Quick View)

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

Sort Array with Excel VBA: 2 Effective Ways

Here we’ve got a data set with the names of some employees of a company called Mars Group.

Our objective today is to learn how we can sort this array with VBA. We’ll learn to sort both A-Z and Z-A.

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

First of all, we’ll sort the array A-Z (In Ascending Order)

First, we’ll convert the selected range from an Excel worksheet into an array.

Dim MyArray As Variant

MyArray = Application.Transpose(Selection)

Next, we’ll 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

Now we’ve sorted the array. Finally, we’ll insert it into the Excel worksheet by another for-loop.

For i = 1 To Selection.Rows.Count

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

Next i

So, 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

⧭ Output:

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

You’ll find the range sorted in ascending order.

⧭ Note:

You can sort an array of numerical values in this too. For example, there may be an array {5, 4, 1, 7, 3}. You can sort it to {1, 3. 4, 5, 7} using the same code.

Read More: Excel VBA Sort Array Alphabetically

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

In the previous section, we sorted an array A-Z (In Ascending Order). This time we’ll sort the array Z-A (In Descending Order).

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

Therefore, 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

⧭ Output:

Select the range to be sorted (B4:B13 here) from the worksheet and run the code.

It’ll sort the selected range in descending order.

⧭ Note: You can sort a numeric array in this way too.

Read More: Excel VBA to Sort in Descending Order

Things to Remember

We used the manual way to sort an array here. Here is a brief explanation of what we did in the code.

Let’s have an array {3, 2 ,5, 4, 6}.

First, we’ve compared 3 and 2. Since 3 is bigger, then we’ve compared 3 with 5.

As 5 is bigger then we’ll compare 5 with 4. As 5 is again bigger, we’ll compare 5 with 6. Now we’ll take 6, as 6 is bigger.

Thus we’ve got the largest value. It’ll be the first member of the sorted array.

Now we’ll repeat the same procedure 4 more times, each time getting the largest value.

Thus we sort the array in descending order.

And for ascending order, we use just the reverse method.

This is the method used to sort an array with VBA. If you want to sort an array directly in the Excel worksheet, you can use the SORT function of Excel.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Using these methods, you can sort an array with VBA in Excel. Do you have any questions? Feel free to ask us.

Related Articles

• Excel VBA to Sort Alphabetically
• Excel VBA to Sort a ComboBox List Alphabetically
• VBA to Sort Table in Excel
  
• Excel VBA to Custom Sort
• [Fixed!!] VBA Sort Not Working in Excel
• Excel VBA to Sort Multidimensional Array