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