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
- Use the range object to specify the range that you wish to sort.
- Type a dot and enter the sort method.
- Specify the key that you want to use to sort.
- Define the sorting order.
- 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.
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.
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 the 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 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
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Apply Borders on a Cell using VBA in Excel
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells