Excel VBA Sort Named Range (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Opening a Visual Basic Editor to sort named range in Excel

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

Creating a Module

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

Space to write the VBA Code


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

Output of Sorting named range in Ascending Order using VBA in Excel

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.

Sorting Named Range in Ascending Order in Excel

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

Code Breakdown

As a first step, I have created a sub_procedure named Ascending_Order_Sorting. Then, I have input the name of my pre-defined named range as the range; Range(“Book_Info”). I have considered cell B5 as the key; key1:=Range(“B5”) to sorting the entire named range keeping column B in ascending order. I also mentioned the command to keep the header intact (Header:=xlYes) in their places.


ii. Sort in Descending Order

Output of Sorting 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.

Sorting Named Range in Descending Order

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

Code Breakdown

Under the sub_procedure named Descending_Order_Sorting, I have input the name of my pre-defined named range Book_Info as the range. I have given the instructions through VBA code to sort column-wise based on column D keeping the header intact in their places.

Read More: How to Sort Range Using VBA in Excel


2. Sort Named Range Using Multiple 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.

Adding Buttons over Column Headers

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

Using Mutilple Column Keys to Sort

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

Code Breakdown

To keep the synchronization with the named range Book_Info, I have created 3 buttons and created three sub-procedures for each button.

For the first button, I have defined a sub_procedure named Button1_Click. I have given the instructions through VBA code to sort column-wise in the ascending order based on column B; key1:=Range(“B5”), order1:=xlAscending, keeping the header intact in their places.

Similarly, I have created sub_procedures named Button2_Click  and Button3_Click for buttons 2 and 3. I have given instructions to those buttons which will sort column C in Descending order by clicking on the column C header and by clicking on the column D header, it will ascend column D. Though the header is inside the named range, I have used Header:=xlYes command to keep the header out of this sorting.

Read More: Excel VBA Sort Range with Multiple Keys


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.

Double Clicking on Header to Sort Named Range

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

Code Breakdown

The whole code is written under a Private_Sub which defines that the code will execute only for this worksheet. The code will run only on double-click which I mentioned through the Worksheet_BeforeDoubleClick event. The Book_Info is the named range that considers range B4:D14.

I have defined three variables where n means the number of named range column’s headers, r means header containing rows, and ColumnCount defines the number of columns within the named range. The applied condition considers only the column headers within the named range and ignores the empty cells on both sides in the header row.

Read More: Excel VBA to Sort by Column Header Name


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.

Sorting Named Range with More than 3 Columns

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

Code Breakdown

We have written the whole code under a Private_Sub which defines that the code will execute only for this worksheet. The code will run only on double-click which I mentioned through the Worksheet_BeforeDoubleClick event. The Car_Info is the named range that considers range B4:E17.

I have defined three variables where n means the number of named range column’s headers, r means header containing rows, and ColumnCount defines the number of columns within the named range.

The applied condition considers only the column headers within the named range and ignores the empty cells on both sides in the header row.

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

Read More: VBA to Sort Column in Excel


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.

Sorting Table by Color with VBA

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

Code Breakdown

Under the Sort_by_Color sub-procedure, I have created a With block. Inside the With block, I have cleared all the previously set SortFields (SortFields.Clear). Then, I have written code to ascend cells in a sequence of red (SortOnValue.Color = RGB(255, 0, 0)), green, and blue colors keeping the headers intact.

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.


Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

2 Comments
  1. I have a worksheet “Grid_Reference_Tables” and a named range “Grid_Reference” A3:B240 with headers in A3:B3. I am adding new data in the first empty row, currently Row 211, then want to sort on Column A in alphabetical ascending order.

    This code fails Compile error Syntax error:-

    Sub Ascending_Order_Sorting()
    ‘Sorting Named Range A-Column-Wise in Ascending Order
    With ActiveWorkbook.Worksheets(“Grid_Reference_Tables”).Range(“Grid_Reference”).Sort _
    key1:=Range(“A4”), _
    order1:=xlAscending, _
    Orientation:=xlSortColumns, _
    Header:=xlYes
    End With
    End Sub

    could you please explain where I am going wrong?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Nov 26, 2023 at 4:47 PM

      Hello ALAN FARTHING

      Thanks for reaching out and sharing your problem. I found a Syntax Error within your given code. You have to write the .sort property within the With block.

      Corrected Sub-procedure:

      
      Sub Ascending_Order_Sorting()
          With ActiveWorkbook.Worksheets("Grid_Reference_Tables").Range("Grid_Reference")
              .Sort key1:=.Columns(1), order1:=xlAscending, Header:=xlYes
          End With
      End Sub
      

      OUTPUT:

      Hopefully, the correction resolves your problem. Stay blessed.

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo