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

Quick View to Sort Array in Excel VBA


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.

Data Set to Sort Array in Excel VBA

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

VBA Code to Sort Array in Excel VBA

⧭ 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.

Output Sort Array in Excel VBA

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


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

VBA Code Sort Array in Excel VBA

⧭ 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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo