Home / VBA / Use to VBA Sort Range in Excel

Use to VBA Sort Range in Excel

- Written by Puneet Gogia

Contents hide
VBA’s Sort Method
Sort a Range with VBA
Sort Descending
Sort a Range but Exclude Headers
Using Multiple Columns to Sort
Changing Sort Orientation
Dynamic Range Sort
Related Tutorials

Let’s say you work in an office, managing employee data. You have a sheet listing all employees, their departments, and birthdays. Every month, you need to send out birthday cards.

Using a VBA code in Excel, you can sort this list by birthday month and day, so you always know whose birthday is coming up next. This makes it quick and easy to stay organized.

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.

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: The primary key by which you want to sort. In the example, Range(“A1”) refers to the first cell in the specified range, which means the data will be sorted based on the values in the first column.
  • Order: This can be xlAscending (for A to Z or smallest to largest) or xlDescending (for Z to A or largest to smallest).
  • Header: A constant specifies whether the first row contains headers. Use xlYes if there is a header, and xlNo if 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.
sort a range by vba
Range("A1:A11").Sort Key1:=Range("A1"), _
                     Order1:=xlAscending, _
                     Header:=xlYes
  • Range(“A1:A11”) specifies the range of cells you want to sort in the worksheet. It includes all cells from A1 to A11 in column A.
  • .Sort is the method that performs the sorting on the specified range. Simply put, the method tells Excel to sort the range with the code.
  • Key1:=Range(“A1”) sets the key for the sort. This means that the sorting will be based on the values found in the column, starting in cell A1.
  • Order1:=xlAscending defines the order of the sort. xlAscending means the data will be sorted from smallest to largest (or alphabetically from A to Z).
  • Header:=xlYes specifies that the first row of the specified range (A1) is a header row. Excel will treat it differently by keeping it at the top and not sorting it with the rest of the data.

Sort Descending

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

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

Sort a Range but Exclude Headers

Sub SortExcludingHeader()
    With ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")
        .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
    End With
End Sub

Using Multiple Columns 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.

Using Multiple Columns to Sort

Here’s the code that you need to use:

codes for multiple column
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.

sort orientation

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

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

Dynamic Range Sort

It finds out how many rows of data you have in column A. Then, it sorts all the data from the first row down to the last filled row in columns A and B.

It sorts in ascending order, from smallest to largest or earliest to latest. The code assumes that the first row has headers consistent with your data. It helps keep lists organized without manually finding the range each time.

Sub DynamicRangeSort()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set sortRange = ws.Range("A1:B" & lastRow)
    sortRange.Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlYes
End Sub
What is VBA

Related Tutorials