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

Download Practice Workbook

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

2 Effective Ways to Sort Array with Excel VBA

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.

Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)

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: How to Sort by Ascending Order in Excel (3 Easy Methods)

Similar Readings:

• How to Auto Sort Table in Excel (5 Methods)
• Merge Cells Using Excel Formula (8 Simple Ways)
• How to Undo Sort in Excel (3 Methods)
• Sort Excel Sheet by Date (8 Methods)
• Random Sort in Excel (Formulas + VBA)

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: How to Sort Excel Tabs in Ascending or Descending Order (2 Ways)

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.

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

• [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
• Excel Sort and Ignore Blanks (4 Ways)
• How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• How to Split a String into an Array in VBA (3 Ways)
• VBA to Transpose Array in Excel (3 Methods)