VBA: How to Sort Values Alphabetically


You can use the following basic syntax in VBA to sort the values in a range alphabetically:

Sub SortAlphabetical()
Range("A1:B11").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

This particular example sorts the rows in the range A1:B11 by the values in column A in alphabetical order (from A to Z).

If you’d like to sort the values in reverse alphabetical order (from Z to A) then you can specify Order1:=xlDescending instead.

Note that Header:=xlYes specifies that the first row should be treated as a header row.

The following example shows how to use this syntax in practice.

Example: Sort Values Alphabetically Using VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to sort the rows based on team name in alphabetical order.

We can create the following macro to do so:

Sub SortAlphabetical()
Range("A1:B11").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub

When we run this macro, we receive the following output:

The rows are now sorted by team name in alphabetical order (from A to Z).

To instead sort the rows in reverse alphabetical order (from Z to A) we can specify Order1:=xlDescending:

Sub SortAlphabetical()
Range("A1:B11").Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlYes
End Sub

When we run this macro, we receive the following output:

The rows are now sorted by team name in reverse alphabetical order (from Z to A).

Note #1: In this example we sorted by one column. However, you can specify more Keys to sort by multiple columns.

Note #2: You can find the complete documentation for the VBA Sort method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Sort Sheet by Multiple Columns
VBA: How to Count Number of Rows in Range
VBA: How to Filter a Column

Leave a Reply

Your email address will not be published. Required fields are marked *