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

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

⧭ Note:

You can sort an array of numerical values too.

Read More: Excel VBA Sort Array Alphabetically

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

⧭ Output:

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

It will sort the selected range in descending order.

Read More: Excel VBA to Sort in Descending Order

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF