Sorting data in Excel using VBA techniques

With VBA, you can create a macro to perform tasks quickly with just one click, saving a considerable amount of time and effort whenever you need to process Excel data. Additionally, when creating a dashboard in Excel, you enhance the ability to sort Excel data to a new level by double-clicking on the header as shown below.

In this article, Mytour will guide you on how to sort data in Excel using VBA.

Mastering Range.Sort in Excel VBA

When sorting data through VBA, understanding the Range.Sort method is crucial. The 'Range' represents the data you want to sort. For instance, if you want to sort data in cells A1:A10, then the 'Range' is that specific range ('A1:A10').

Additionally, you can create a named Range and use it instead of cell references. For example, if I create a named range 'DataRange' for cells A1:A10, I can use Range('DataRange').

With this data sorting method, you need to provide additional information through parameters. Here are some key parameters you should be aware of:

- Key - Here, you need to specify the column you want to sort. For instance, if you want to sort column A, use the command key:=Range('A1').

- Order - In this case, you choose to sort the data in ascending or descending order. For example, to sort in ascending order, use the command Order:=xlAscending.

- Header - Here, you select whether the data set has a header or not. If there is a header, sorting starts from the second row of the data set instead of the first. For data with a header, use the command Header:=xlYes.

These three parameters apply to most cases; however, Mytour suggests exploring additional parameters in Excel in this article.

Now, let's explore how to sort data in Excel using VBA with the Range.Sort method!

Sorting Each Column Without a Header

Imagine you have a column without a header (as shown in the illustration).

You can use the following code snippet to sort it in ascending order.

Sub SortDataWithoutHeader()
Range('A1:A12').Sort Key1:=Range('A1'), Order1:=xlAscending, Header:=xlNo
End Sub
Here, I choose the data range as Range('A1:A12').
If your data can change and values can be added or removed, you can use the following code to dynamically adjust to cells with data in the data set.
Sub SortDataWithoutHeader()
Range('A1', Range('A1').End(xlDown)).Sort Key1:=Range('A1'), Order1:=xlAscending, Header:=xlNo
End Sub

Note that instead of Range('A1:A12'), I've used Range('A1', Range('A1').End(xlDown)).

This way, we will check cells with continuous data down to the end of the column, encompassing it in the sorted data. In case of empty cells, the program will only consider data up to the first empty cell.

Additionally, you can create a named range and use that named range instead of cell references. For example, if the range is named DataSet, your code would be written as follows:

Sub SortDataWithoutHeader()

Range('DataRange').Sort Key1:=Range('A1'), Order1:=xlAscending, Header:=xlNo

End Sub

Now, let's quickly explain the parameters used in the example above:

- Key1:=Range('A1') - A1 signifies the column to sort data.

If you're wondering where to put this VBA code and how to run the macro, continue reading the article on How to Sort Data in Excel using VBA!

Sorting a Single Column with a Header

In the previous example, the data set had no header.

For data with headers, you need to specify in the code that the data sorting should start from the second row of the data set.

Imagine you have a data set like this:

Here is the code to sort data in descending order based on the revenue of the stores.

Sub SortDataWithHeader()
Range('DataRange').Sort Key1:=Range('C1'), Order1:=xlDescending
End Sub

Note that I just created a named range - 'DataRange' and used this named range in the code.

Sorting Multiple Columns with Headers

In the previous sections of this article, we delved into sorting single columns (with and without headers).

Now, let's explore the steps you need to take to sort data across multiple columns.

For example, in the data set below, I need to first sort the data by state code, and then by store.

Here is the code for sorting multiple columns simultaneously.

Sub SortMultipleColumns()
With ActiveSheet.Sort
.SortFields.Add Key:=Range('A1'), Order:=xlAscending
.SortFields.Add Key:=Range('B1'), Order:=xlAscending
.SetRange Range('A1:C13')
.Header = xlYes
.Apply
End With
End Sub

Here is the result you obtain.

In the example above, first, the data is sorted by state code (column A). Then, within the state code data, it continues to be sorted by Store Name (column B). This order is determined by the code you want to reference.

Double-Click to Sort Data

If you are creating a dashboard or want to enhance report data exploration, you can write VBA code to sort data when double-clicking on column headers.

As shown in the table below:

Here is the code to perform this task:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range('DataRange').Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= columncount=''>
Cancel = True
Set KeyRange = Range(Target.Address)
Range('DataRange').Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Note that I just created a named range ('DataRange') and used it in the code instead of cell references.

When you double-click any header, the code will disable the usual double-click functionality (switch to edit mode) and use that cell as the key for sorting the data.

Additionally, this code only sorts the columns in ascending order.

Placement of the Code

You need to paste this code into the code window of the worksheet where you want to implement the double-click sorting feature.

Steps to perform:

- Right-click on the tab in the worksheet.
- Click on View Code.
- Paste the code into the code window of the worksheet where you want to sort the data.

Suppose now you want to sort the first two columns ('State' and 'Store') in ascending order and the 'Sales' column in descending order.

The code will look like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range('DataRange').Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= columncount=''>
Cancel = True
Set KeyRange = Range(Target.Address)
If Target.Value = 'Sales' Then
SortOrder = xlDescending
Else
SortOrder = xlAscending
End If
Range('DataRange').Sort Key1:=KeyRange, Header:=xlYes, Order1:=SortOrder
End If
End Sub

In the above code, you need to check if the double-clicked cell is indeed the 'Sales' header. If true, it assigns the value xlDescending to the SortOrder variable; otherwise, it assigns xlAscending.

Now, let's observe the visual Marker tool (arrow and colored cell) in the header when sorting data.

As shown in the image below:

So, I just added a new worksheet and modified some content in this table (you can download the example file and practice along):

- Rename the new worksheet to 'BackEnd'.
- In cell B2, enter the arrow symbol (how to: go to Insert and click on 'Symbol' option).
- Copy and paste the headers from the data set into cells A3:C3 in 'BackEnd'.
- Use the following function in cells A4:AC4:
=IF(A3=$C$1,A3&' '&$B$1,A3)
- The remaining cells will automatically fill with data using VBA code when you double-click on the headers to be sorted.

The backend table will look like this:

Now, you can use the code below to sort data when double-clicking on the headers. When you double-click on a header, it will automatically display an arrow in the header text. Note that I used conditional formatting to highlight the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range('DataRange').Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= columncount=''>
Cancel = True
Worksheets('Backend').Range('C1') = Target.Value
Set KeyRange = Range(Target.Address)
Range('DataRange').Sort Key1:=KeyRange, Header:=xlYes
Worksheets('BackEnd').Range('A1') = Target.Column
For i = 1 To ColumnCount
Range('DataRange').Cells(1, i).Value = Worksheets('Backend').Range('A4').Offset(0, i - 1).Value
Next i
End If
End Sub

Please note that this code is typically used for constructing data and workbook files. If you alter the data structure, you'll need to modify the entire code snippet.

Here, we've assisted you in sorting data in Excel using VBA. To further solidify your understanding, refer to how to access data in Excel worksheets from VBA here. Wishing you success!