VBA: How to Sort Sheet by Multiple Columns


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

Leave a Reply

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