# 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
`````` ## 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.

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

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

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

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

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

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 