You can use the following basic syntax to sort an Excel sheet by multiple columns using VBA:
Sub SortMultipleColumns()
Range("A1:B11").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlDescending, _
Header:=xlYes
End Sub
This particular example sorts the cells in the range A1:B11 first by the values in column A ascending, then by the values in column B descending.
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 Excel Sheet by Multiple Columns Using VBA
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to sort the dataset in the following manner:
- First, sort by the values in the Team column ascending (A to Z)
- Then, sort by the values in the Points column descending (largest to smallest)
We can create the following macro to do so:
Sub SortMultipleColumns()
Range("A1:B11").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlDescending, _
Header:=xlYes
End Sub
When we run this macro, we receive the following output:
The rows are now sorted by team name in ascending order and points in descending order.
Note #1: In this example we sorted by two columns. However, you can specify as many Keys as you would like to sort by even more 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 Write AVERAGEIF and AVERAGEIFS Functions
VBA: How to Write SUMIF and SUMIFS Functions
VBA: How to Write COUNTIF and COUNTIFS Functions