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 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
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel
- How to Select a Range/Cell using VBA in Excel
If you are preparing for an interview, make sure to check out these VBA interview questions.