Table of Contents
VBA (Visual Basic for Applications) has a built-in function called “Sort” which can be used to sort a range of cells alphabetically. This function requires two arguments: the range of cells to be sorted and the sorting order (ascending or descending). The range argument can be a single cell, a row, a column, or a range of cells. Once the arguments have been passed to the Sort function, it will automatically arrange the values within the range alphabetically according to the sorting order specified.
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
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 .