Excel VBA Sort Named Range (3 Examples)

Looking for ways to sort named range using VBA in Excel? Then, this is the right place for you.

In Microsoft Excel, a Named Range is a set of cells with a specified name or label. In terms of working with a huge amount of data, we normally use a named range to define a certain amount of cells. We can sort the data inside the named range quite easily with VBA. In the above video, I have just shown a demo of it. For more details, go through the below section.

Download Practice Workbook

You can download the practice workbook from here.

How to Launch VBA Editor in Excel

We often create a module in order to work with VBA. First of all, we need to go to the Developer tab and select Visual Basic to create a module.

Once you have opened the editor, then click on the Insert tab and pick the Module option.

A module will be created and we can write the required code in that module to execute the program.

Range.Sort Method to Sort a Named Range in Excel VBA

Named Range is actually nothing but a range of cells with a defined name or label. In terms of working with a huge amount of data, we can easily identify a certain range of cells with a named range. It is highly used in formulas, charts, and other operations. With the help of VBA, we can sort the quite easily. There are generally two types of sorting: Ascending order and Descending order. We sort them based on a certain column or row within the named range.

Range("Named_Range").Sort _
key1:=Range("B5"), _
order1:=xlAscending, _
Orientation:=xlSortColumns, _
Header:=xlYes

According to the code, a predefined named range is to be used as a range. Under key properties, we need to define the column based on which we want to do the sorting. Sorting can be ascending or descending which needs to be mentioned in order. We can sort based on column or row. We need to define that from the Orientation section. If we include the headers in the named range and keep the headers unchanged, we need to input xlYes from the Header section.

Excel VBA Sort Named Range: 3 Examples

In order to sort named range, we can apply sort properties with the desired specifications. We can apply the VBA code by running a module, clicking on the header button, or double-clicking on the header.

For more clarification, I have created the Book_Info named range which defines the range B4:D14. All the methods are described in the following section.

1. Sort Named Range with VBA

Sorting is mainly done in two ways. Either in Ascending order or in Descending order. They are described in detail in the below section.

i. Sort in Ascending Order

Normally, we prefer to sort data either smallest to largest or largest to smallest. If we sort data from the smallest to the largest, it is called the Ascending order. To sort a named range in ascending order, you can use the following VBA code.

Sub Ascending_Order_Sorting()
'Sorting Named Range B-Column-Wise in Ascending Order
Range("Book_Info").Sort _
key1:=Range("B5"), _
order1:=xlAscending, _
Orientation:=xlSortColumns, _
Header:=xlYes
End Sub

ii. Sort in Descending Order

Descending order is nothing but a way of sorting from the largest to the smallest. We normally sort a certain column in descending order and the related data automatically shifts with the reference cells. The code to do so is listed below.

Sub Descending_Order_Sorting()
'Sorting Named Range D-Column-Wise in Descending Order
Range("Book_Info").Sort _
key1:=Range("D9"), _
order1:=xlDescending, _
Orientation:=xlSortColumns, _
Header:=xlYes
End Sub

2. Sort Named Range Using Mutilple Column Keys

We can apply a VBA code not only through modules only. There are some other ways too. One of them is through the Command Button. We can assign our VBA to the button and click on it to execute. We can place the buttons as column headers. In this section, I will apply multiple codes for multiple buttons and place them over the headers. The code will execute and sort the named range based on the click over the column header.

For the execution of this plan, we need to insert the command button first as the column header. For this, follow the following procedures.

• Click on the Developer tab as the first step to insert a command button.
• Next, click on Insert from the ribbon.
• From the available options, click on Button from the Form Controls

With the repetition of similar steps, create as many buttons as you need and place them over the headers.

Now, assign macros to each of the buttons which will execute just by clicking on them.

Sub Button1_Click()
'Sorting Named Range B-Column-Wise in Descending Order
Range("Book_Info").Sort _
key1:=Range("B5"), _
order1:=xlAscending, _
Header:=xlYes
End Sub
Sub Button2_Click()
'Sorting Named Range C-Column-Wise in Descending Order
Range("Book_Info").Sort _
key1:=Range("C5"), _
order1:=xlDescending, _
Header:=xlYes
End Sub
Sub Button3_Click()
'Sorting Named Range D-Column-Wise in Ascending Order
Range("Book_Info").Sort _
key1:=Range("D5"), _
order1:=xlAscending, _
Header:=xlYes
End Sub

3. Double Click on Header to Sort Named Range

We can sort a named range just by double-clicking on the column header. We can certainly  do that. For this, we need to assign the VBA code inside a private_sub and select BeforeDoubleClick from the Procedure. Then, apply the following VBA code to execute the plan.

Private Sub Worksheet_BeforeDoubleClick(ByVal Desired_Range As Range, Cancel As Boolean)
Dim Book_Info As Range
Dim ColumnCount As Integer
'Variable Declaration along with Values
Set Book_Info = Range("B4:D14")
n = Book_Info.Cells(1, 1).Column - 1
r = Book_Info.Cells(1, 1).Row
ColumnCount = Book_Info.Columns.Count
Cancel = False
'Apply Condition to Sort Based on Double-click on Header
If Desired_Range.Row = r And Desired_Range.Column <= ColumnCount + n And Desired_Range.Column > n Then
Cancel = True
Set Book_Info = Range(Desired_Range.Address)
Book_Info.Sort key1:=Desired_Range, Header:=xlYes
End If
End Sub

Sort Named Range with More Than 3 Columns with Excel VBA

Actually, we can modify our code to sort named range with more than 3 columns. The following code is set as dynamic for the named range in any place in the worksheet. It will ignore the header row’s empty cells on both sides and and consider the cells in the first row within the named range as headers.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Car_Info As Range
Dim ColumnCount As Integer
'Variable Declaration along with Values
Set Car_Info = Range("B4:E17")
n = Car_Info.Cells(1, 1).Column - 1
r = Car_Info.Cells(1, 1).Row
ColumnCount = Car_Info.Columns.Count
Cancel = False
'Apply Condition to Sort Based on Double-click on Header for more than 3 columns
If Target.Row = r And Target.Column <= ColumnCount + n And Target.Column > n Then
Cancel = True
Set Car_Info = Range(Target.Address)
Car_Info.Sort key1:=Target, Header:=xlYes
End If
End Sub

Finally, double-clicking on the column header to sort the named range in ascending order.

Sort Table by Color with Excel VBA

In the above section, I have explained the sorting method based on value. We can also consider the color as a parameter to do the sorting of a table. I have shown a demo in the above video. Now, apply the following code to sort table based on color.

Sub Sort_by_Color()
'Use With Block to Sort Named Range B-Column-Wise in Color Order
With ActiveWorkbook.Worksheets("Sort_by_Color").Sort
.SortFields.Clear
.SortFields.Add(Key:=Range("B5"), _
SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(255, 0, 0)
.SortFields.Add(Key:=Range("B5"), _
SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(0, 255, 0)
.SortFields.Add(Key:=Range("B5"), _
SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(0, 0, 255)
.SetRange Range("B4:E17")
.Header = xlYes
.Apply
End With
End Sub

Note: The first and foremost condition to apply this method is that you must have color filled cells in the applied column of the table.

Sort Not Working in VBA

There might be several cases when sorting will not work with VBA. Some of them are listed below:

• Sort Named Range Including Headers

It is a very common mistake that we define the entire table as a named range and give no command to keep the header intact. As a result, the named range gets sorted along with the cells containing headers.
To solve this problem, we need to give the following command to keep the headers intact.

Header:=xlYes

This will keep the headers intact and will not consider while sorting.

• Use ActiveSheet Object and Keep Another Sheet Active

We may face the problem that sorting is not working while running the VBA code. It might happen when we apply the ActiveSheet object and keep another sheet active.

To solve this problem, we need to keep an eye to keep the relevant worksheet active while using the ActiveSheet object.

Conclusion

In the above article, I have tried to explain the named range sorting in either ascending or descending order by running a macro, double-clicking on the headers, or clicking on the button. I  have also shown some common mistakes and their solutions while sorting. I hope it will be a very helpful article for you if you are wanting to sort a named range. For more Excel-related articles, you can visit our site exceldemy.com.