VBA Sort Range | (Descending, Multiple Columns, Sort Orientation

Home ➜ VBA ➜ VBA Sort Range | (Descending, Multiple Columns, Sort Orientation

To sort a range of cells using VBA, you need to use the “SORT” method that allows you to set a single key (column) or multiple keys (for multiple columns) to sort. You can also define the order (ascending or descending) in which you want to sort, and you can specify if you have a header or not.

Sort a Range with VBA

1. Use the range object to specify the range that you wish to sort.
2. Type a dot and enter the sort method.
3. Specify the key that you want to use to sort.
4. Define the sorting order.
5. Mention if you have a header or not.

Range("A1:A11").Sort Key1:=Range("A1"), _
                     Order1:=xlAscending, _
                     Header:=xlYes

In the above code, you have used the range A1:A11, and in the sort method, you have used the ascending order, mentioned that you have a header in the range.

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Understanding the VBA’s Sort Method

Before you write a macro to sort a range it’s better to make deep dive into the sort method and understand its arguments.

Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, _
Header, OrderCustom, MatchCase, Orientation, SortMethod, _
DataOption1, DataOption2, DataOption3)

The following are the most important arguments that you will be using in the real life.

• Key: Here you need to define a cell whose column you want to use as a base to sort.
• Order: Order in which you want to sort the column (ascending/descending).
• Header: A constant to specify if you have a header or not or you want VBA to guess.

Sort Descending

In the following code, I have used the “xlDescending” to sort amount column using descending order.

Range("A1:A13").Sort Key1:=Range("A1"), _
                     Order1:=xlDescending, _
                     Orientation:=xlSortColumns

Using Multiple Columns (Keys) to Sort

You can also use more than one column to sort a range. Let’s take an example of the below table where you have multiple entries with employee names and cities, and you need to sort using the name and city.

Here’s the code that you need to use:

Range("A1:C13").Sort Key1:=Range("A1"), _
                     Order1:=xlAscending, _
                     Key2:=Range("B1"), _
                     Order1:=xlAscending, _
                     Header:=xlYes

As you can see in the code, you have key1 and key2. In the key1, you have the column with employee name, and in the key2, you have the city column. For both columns, the sorting order is ascending, and headers are there.

Note: You can use it as any column you want to use to take as a sort base using the keys and order.

Changing Sort Orientation

When you normally sort data in Excel, by default, you can sort using columns. But from the sort options, you can change the sort orientation to the left to right instead of top to bottom.

In the same way, you have an argument in the sort method to change the orientation (link).

Range("A1:C13").Sort Key1:=Range("A1"), _
                     Order1:=xlAscending, _
                     Orientation:=xlSortRows