Sort Range using VBA – Table Header

We can sort a range of cells in excel using simple ways like the Sort option or SORT Function. VBA is required to sort a range when we need to automate the sorting and limit it to only a single click for weekly or monthly reports. We can sort range using VBA by creating table headers which when double clicked, sort the table in a defined order of that column.

So let us start learning.

Range.Sort Method in VBA

Sort is the method of Range Object in VBA that is used to sort a range of cells specified in the Range. For instance, if we need to sort the range A1:A5 then Range would be Range(“A1:A5”). We can also use a named range in double quotes.

You can also download the practice workbook to follow along with the article.

Syntax and Arguments

The sort method of the Range object requires the following parameters.

• Key1 – This is a required parameter. We need to specify the column on the basis of which we want to sort the entire range. If we want to sort according to values in Column A, then the key would be Range(“A1”).
• [Order1] – This is to tell the order for key1. Use xlAscending to sort key1 in ascending order and xlDescending to sort key1 in descending order.
• [Key2] – This is the second key.
• [Order2] – This is the sorting order for key2.
• [Header] – Use xlNo if the range does not contain a header otherwise the value would be xlYes. It is an optional argument. If the table has headers then the sorting would start from the second row and not from the table headers.

While passing each parameter in the sort method, we would specify the parameter name followed by := and then the value for that parameter.

Range("<range>").Sort(Key1:=<value>, Order1:=<value>, Header=<value>)

Examples to Sort Range using VBA

Let us suppose we have the following data range as follows.

You can see that there are three columns and the headers are highlighted. Create a named rang from this data. To create a named range, follow these steps

• Select the range A1:C13.
• Go to Name Box and type MyRange as the name of range A!1:C13. Hit Enter key.

You can do the entire work without creating a named range by using the Select property of Range Object or using Dynamic Named Range. For now, to keep it simple, we are using a static named range.

Example 1 – Sort using a Single Column

In this example, let us suppose we want to sort the data based on increasing order of the Quantity. Use the following VBA code.

• Press Alt and F11 keys. Go to Insert Tab and click on Module. Place the following code in the new module1.

Sub Sort_Qty()
'sort a range based on one column
Range("MyRange").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes
End Sub

Here the key1 is referring to cell C1 (Quantity column ), Order1 is the sorting order for quantity and Header is present in MyRange so Header:=xlYes.

• Press Alt and F8 keys and select Sort_Qty to run it.

This sorts the order based on increasing order of Quantity.

Example 2- Sort Based on Two Columns

We can first sort the orders based on the Item in ascending order (A to Z) and then Sub Sort the Quantity in Descending order ( High to Low for Each Item ). Use the following code to do so.

Sub Sort_Item_Qty()
'sort range based on two columns
Range("MyRange").Sort key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("C1"), Order2:=xlDescending, Header:=xlYes
End Sub

We specify the Key1 to refer to column B containing Items. Order1 is the order for column B Items. Key2 is for Sub Sorting and we took column C for quantity. Order2 for Quantity is Descending and Header is xlYes.

Run this sub procedure and this is how MyRange gets sorted as a result.

Double Click on the Header to Sort the Range

This is an amazing functionality as it lets you sort the range based on the column header you double click on.

We will use the Worksheet_BeforeDoubleClick event handler for this. The code will be placed in the corresponding Sheet Module.

• Right Click on the Sheet Tab and click on the View Code option.

• Paste the following code in the Sheet1 Module that contains MyRange.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'double click on header to sort the range
Dim ColumnCount As Integer
'ColumnCount contains Total Number of Columns in MyRange
ColumnCount = Range("MyRange").Columns.Count
Cancel = False
'check if Double Clicked cell row number is 1 and column number<=3 (is from range A1:A3)
If Target.Row = 1 And Target.Column <= ColumnCount Then
    Cancel = True
    Dim KeyRange As Range
    'keyrange contains the address of target cell (Column Header which is double clicked)
    Set KeyRange = Range(Target.Address)
    'sort MyRange based on Double Clicked Column Header
    Range("MyRange").Sort key1:=KeyRange, Header:=xlYes
End If
End Sub

You must read the comments for each line of code in this Sub Procedure. This Event Handler executes when the user double clicks on any cell of Sheet1, it checks if the target cell ( cell just double clicked ) is one of the column Headers and then sorts the range based on it. If it is not a column header, the event handler would execute but the condition would be False and nothing happens.

This brings us to an end.

Thank you for reading.❤