Excel VBA to Sort by Column Header Name (5 Easy Ways)

It’s a common practice to sort data in Excel by a particular column header name, especially when working with large datasets. Excel has a built-in sorting capability, but when dealing with complicated sorting rules, utilizing VBA to sort data based on a column header name can be quicker and more effective.

In this article, we’ll take a brief introduction to the VBA Range.Sort method and cover the fundamentals of sorting data using VBA in Excel, including how to specify the range and column header name, how to select the sort order, and how to handle multiple column headers. We’ll also explore how to combine the VBA Sort method with the VBA Match function and VBA Find method and learn to sort by color.

The above video is an overview of this article, which represents how to sort by column header name in Excel VBA. Here, by double-clicking the “Company” and “Revenue (Billions)” column headers, the data gets sorted in ascending order.

How to Launch and Insert Code in Visual Basic Editor in Excel

The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point in the world of VBA; so let’s begin.

📝 Note: By default, the Developer tab remains hidden. You can enable the Developer tab by following this linked article.

Moving to the developer tab and clicking on the visual basic button

Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.

Inserting module in the visual basic for applications window

This launches the Microsoft Visual Basic for Applications window. Now, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code, and we hit the Run button or F5 key to execute the code.

The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can also limit our code to a specific worksheet.

Introduction to VBA Range.Sort Method in Excel

VBA’s Range.Sort method sorts a range of values in Excel. Here, the Range is an object variable that specifies the range of cells we want to sort in ascending or descending order. The Range.Sort method also supports Named Ranges inside double quotes.

  • Syntax:
Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
  • Arguments:
Key Optional Specifies the range or the column header to be sorted.
Order Optional Specifies the order of the sorting.

  • xlAscending = To sort in ascending order.
  • xlDescending = To sort in descending order.
Type Optional Specifies the PivotTable elements to be sorted.
Header Optional Specifies whether the first row contains headers or not.

  • xlNo = without column headers; Default value.
  • xlYes = columns have headers.
  • xlGuess = Excel determines the headers.
MatchCase Optional
  • True = case-sensitive sort
  • False = non-case-sensitive sort
Orientation Optional
  • xlSortColumns = sort by columns
  • xlSortRows = sort by rows
SortMethod Optional Specifies the method of sorting.
DataOption Optional Specifies sorting of text in the Key1 argument

Excel VBA to Sort by Column Header Name: 5 Different Examples

In this portion, we’ll discuss five ways to sort by column header name in Excel VBA. For each method, we’ll start with a brief description of the dataset, where applicable, then show an overview of the result, including the VBA code explanation, and lastly show a live demonstration of running the VBA code. So, let’s see each example in detail with the appropriate illustration.

1. Sorting a Single Column by Header Name

This method demonstrates the ways of sorting a single column either in ascending order or descending order by header name.

Dataset showing a list of companies and their revenues earned

Let’s introduce our dataset first. We have the Revenue Earned by Top 15 Companies dataset shown in the B4:C19 cells, which depict the Company names, and the Revenue (Billions) columns respectively.

1.1 Ascending Order

Sorting data in ascending order by column header name using Excel VBA

In this situation, the Revenue (Billions) column gets sorted in ascending order. We can observe and compare the sorted and unsorted data results as shown in the picture.

VBA code to sort data in ascending order by column header name

Now, to apply this VBA code, follow the steps shown previously to insert a Module. To do this, insert the code into the Module window >> press the Run button or hit the F5 key.

Sub Ascending_Sort()
'Using Range.Sort method to sort in ascending order
Range("E5:F20").Sort Key1:="Revenue (Billions)", _
Order1:=xlAscending, Header:=xlYes
End Sub

Code Breakdown:

  • First, declare the sub procedure named Ascending_Sort.
  • Next, define the E5:F20 range to be sorted using the Range object.
  • Second, use the VBA Range.Sort method to assign the Revenue (Billions) column header name in the Key1 argument.
  • Mention the xlAscending (ascending order) in the Order1 argument and set the Header argument to xlYes, indicating column headers are present in the dataset.

The video below shows the steps to sort the data in ascending order by column header name.

Read More: Excel VBA to Sort Column Ascending

1.2 Descending Order

Sorting in descending order by column header name using Excel VBA

With this VBA macro, we can sort the data in descending order. You can see the unsorted and sorted data side-by-side.

VBA code for sorting data in descending order by column header name in Excel

Similar to the previous sorting, the arguments in this code are virtually the same, we just need to change the Order1 argument to xlDescending (descending order). Since this code is similar to the previous method, check the above explanation.

Sub Descending_Sort()
'Using Range.Sort method to sort in descending order
Range("E5:F20").Sort Key1:="Revenue (Billions)", _
Order1:=xlDescending, Header:=xlYes
End Sub

The embedded video demonstrates the process of sorting the data in descending order.

Read More: VBA to Sort Column in Excel

2. Sorting by Double-Clicking Column Header Name

Sorting by double-clicking column header using VBA in Excel

With the use of VBA code, you can easily sort the data by double-clicking on the Company or Revenue (Billion) column headers.

📝Note: The VBA Sort method performs alphabetic sorting to columns containing text data.

Right-clicking worksheet and clicking view code option

Right-click on the Double Click worksheet and choose the View Code option to open a Worksheet event.

VBA code for double-clicking column header to sort data in Excel

At this point, it’s better to discuss Worksheet events; Excel’s Worksheet events are actions that start a specific macro corresponding to that event. For example, consider the Worksheet_BeforeDoubleClick which activates every time the user double-clicks a particular item in that worksheet.

📝Note: Worksheet events are available to a specific worksheet, which means this code cannot be used in other worksheets.
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rng As Range
Dim count As Integer
'count the columns in the range,
'by default double click event is not canceled
count = Range("B4:D19").Columns.count
Cancel = False
'checks if double click cell is in row 4 and
'column is less than equal to count, then cancel event
If Target.Row = 4 And Target.Column <= count Then
Cancel = True
'rng variable is set to Traget object and sorted
Set rng = Range(Target.Address)
Range("B4:D19").Sort Key1:=rng, Header:=xlYes
End If
End Sub

Code Breakdown:

  • The first line shows that this is a BeforeDoubleClick event handler that will run each time a user double-clicks a cell in the worksheet containing the code.
  • Declare the “rng” and “count” variables and assign Range and Integer data types.
  • The If Then statement determines whether the cell that was double-clicked is in row 4 or it is less than the column count of the range. If so, Cancel is set to True in order to stop the double-click event from occurring.
  • The macro sets the “rng” variable to reference the double-clicked cell and the “rng” to the Key1 parameter. Then the VBA Range.Sort method to sort the B4:D19 range.

You can watch the video, where double-clicking the Company or Revenue (Billion) column headers activates the event and sorts the data.

3. Combining VBA Match Function and Sort Method to Sort Column Header by Name

Sorting by column header name with VBA match function and sort method 

We can also combine the VBA Match function and Sort method to sort column by header name. In this scenario, the macro sorts the “Company” column.

VBA code for sorting with match function and sort method 

Unlike the previous examples, this code can handle issues like a non-existent column header and prompt the user to enter the correct column header.

Sub Match_Sort()
Dim wbk As Workbook
Dim wsht As Worksheet
Dim sort_rng As Range
Dim sort_val As Long
Dim rng_val As Range
'enter the column header name
Const col_header As String = "Company"
'set workbook and dataset range
Set wbk = ThisWorkbook
Set wsht = wbk.Worksheets("Match and Sort")
Set sort_rng = Range("E5:F20")
On Error GoTo error_handling
'locate column header with Match function
sort_val = Application.Match(col_header, sort_rng.Rows(1), 0)
Set rng_val = sort_rng.Rows(1).Cells(sort_val)
'apply Sort method to sort column header in ascending order
With wsht.Sort
sort_rng.Sort Key1:=rng_val, Order1:=xlAscending, Header:=xlYes
End With
Exit Sub
'handles invalid column name
Select Case Err.Number
Case 13
MsgBox "Column header does not exist"
'handles other errors
Case Else
Debug.Print Err.Number & Err.Description
End Select
End Sub

Code Breakdown:

  • Initially, declare the variables, enter the column header name, and select the worksheet and dataset range. It is a good practice to use the VBA SortFields.Clear method to clear any previous sorting.
  • Use the VBA Application.Match function to locate the column header name and set the “rng_val” to the column header range.
  • Now, use the VBA Sort method to sort the column header in ascending order.
  • Additionally, the error handling block handles issues faced when running this code. For instance, if the user enters an invalid column header, then the program displays an appropriate message.

The video below shows all the steps described so far.

4. Utilizing VBA Find and Sort Methods to Sort by Column Header Name

Sorting by column header name with VBA find and sort methods

Another alternative to sorting data by column header involves using the VBA Find and Sort methods. Here, the program sorts the “Year Founded” column in ascending order.

VBA code for sorting with find and sort methods

This code is remarkably similar to the previous example and is able to handle user input errors.

Sub Find_Sort()
Dim sort_ads As String
'locate column containing "Year Founded" header
On Error GoTo error_handling
Rows("4:4").Find(What:="Year Founded", After:=Range("B4"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
On Error GoTo 0
sort_ads = ActiveCell.Address(0, 0)
'Set sort range by using current region
Range("B4").CurrentRegion.Sort Key1:=Range(sort_ads), _
Order1:=xlAscending, Header:=xlYes
Exit Sub
'handles invalid column name and other errors
If Err.Number = 91 Then
MsgBox "The column header does not exist", vbOKOnly
MsgBox Err.Number & ": " & Err.Description
End If
End Sub

Code Breakdown:

  • Use the VBA Find method to locate the column in row 4 with the column header “Revenue (Billions)”.
  • The LookIn, LookAt, and SearchOrder arguments define how to search for the column header name, while the After option sets the starting point for the search as cell C4.
  • The matching cell is enabled and its address is saved in the sort_ads variable if the header is located.
  • Utilize the VBA Sort method to arrange the information in ascending order. The Key1 argument designates the column to be used for sorting, while the Header parameter denotes the presence of a header row in the data.
  • If the column header name cannot be found or if some other error occurs, the error handling section displays an error message.

At this point, see the video, which recaps all the steps to run this code.

5. Sorting Multiple Columns by Header Names

Dataset showing employee name, salary, and joining date

Assuming the Employee Information dataset is in the B4:D14 cells, which contain the employee Name, Salary, and Joining Date.

Sorting by multiple column header names

We can also sort multiple columns by their column header names. On this occasion, the program sorts the “Salary” and “Joining Date” columns in descending and ascending order respectively.

VBA code for sorting by multiple column header names

In this case, we’ll apply the VBA Range.Sort method in the “Multiple Columns” worksheet, and declare the “Salary” and “Joining Date” columns to Key1 and Key2 arguments. Afterward, assign descending and ascending orders to the column header names.

Sub Multi_Columns_Sort()
'removes previous sort fields
Worksheets("Multiple Columns").Sort.SortFields.Clear
'Sorting column G in descending and column H in
'ascending order
Range("F5:H15").Sort Key1:="Salary", _
Key2:="Joining Date", Header:=xlYes, _
Order1:=xlDescending, Order2:=xlAscending
End Sub

Observe the steps in the video to sort multiple columns by column header names.

Read More: How to Sort Multiple Columns with Excel VBA

How to Sort by Column Without Header in Excel VBA

Sorting column without column header

The following VBA program above helps you sort a single column without any header in your Excel spreadsheet.

VBA code for sorting column without column header

In this situation, we’ll set the starting range and use the VBA Sort method. One exception is that the Header argument is set such that it shows the absence of a column header.

Sub Sort_Without_Column_Header()
Range("B4", Range("B4").End(xlDown)).Sort _
Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo
End Sub

Follow the instructions in the video to sort the column without any column headers.

How to Sort by Specific Color in Excel VBA

Sorting by color

Thus far we’ve seen the sorting of text and values, but we can sort by color as well with Excel VBA. In the above image, we’ll sort the “Revenue (Billions)” column such that the blue color cells are shown before the green and gold colors.

Inserting excel table with keyboard shortcut

Here, we’ll begin by creating an Excel Table using the CTRL + T shortcut keys.

Renaming Excel table and changing fill color of cells

Now, rename the Table to “Revenue_tbl” and change the fill color of the cells.

You can choose the “Blue, Accent 1, Lighter 60%”, “Green, Accent 6, Lighter 60%”, and “Golden, Accent 4, Lighter 60%” colors respectively.

VBA code for sorting by color

At this time, we’ve used specified the Table (“Revenue_tbl”) and the column header (“Revenue_tbl[Revenue (Billions)]”). In turn, the VBA SortFields method has been employed. To declare the sort column, order, and color; To ensure that the blue color appears at the top enter its RGB code (221,235,247) in the VBA SortOnValue.Color method.

Sub Color_Sort()
Dim wsht As Worksheet
Dim table As ListObject
Dim sort_col As Range
'set workbook and dataset range
Set wsht = ActiveSheet
Set table = wsht.ListObjects("Revenue_tbl")
Set sort_col = Range("Revenue_tbl[Revenue (Billions)]")
'apply Sort method to sort by color in ascending order
With table.Sort
.SortFields.Add(Key:=sort_col, Order:=xlAscending, _
SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(189, 215, 238)
.Header = xlYes
End With
End Sub

The following video portrays a live demonstration of color sorting.

Frequently Asked Questions (FAQs)

  • How can I sort data in Excel using VBA based on a specific column header name?

Excel data can be sorted using the VBA Range.Sort method depending on the name of a particular column header. First, use the VBA Range.Find method to determine the header’s column index then apply this index in the VBA Sort method to sort the data.

  • How do I specify the sort order in VBA?

The Order argument of the VBA Sort method can be used to set the sort order. For instance, setting Order:=xlAscending would sort the data in ascending order, while setting Order:=xlDescending will sort the data in descending order.

  • Can I sort data based on multiple column headers in VBA?

With VBA, you can sort data according to multiple column headings by specifying the sort order for each column header using the VBA Sort method.

  • Can I sort data in a specific range using VBA?

Indeed, you may use the VBA Range.Sort method to sort data within a particular range. Simply use this parameter when sorting your desired range.

  • Can I sort data using VBA without changing the original data?

By transferring the original data to a new range and sorting the copy instead of the original data. In this manner, the original data is left untouched, and you are able to work with the sorted data independently.

Things to Remember

  • To prevent mistakes or unexpected outcomes, it’s crucial to give the correct range and column header name when using VBA to sort data in Excel.
  • The VBA Range.Find method will return a null value if the column header name you are looking for is not present in the given range.
  • Make sure to change the Worksheet and Table names when using the VBA code.
  • When sorting data in VBA, be sure to use the Order parameter, which can be set to either xlAscending or xlDescending, to determine the proper sort order.
  • Worksheet events run a specific event on that worksheet; therefore, make sure to choose the correct event.

Wrapping Up

  • VBA’s Range.Sort method can sort a range of values in ascending and descending order.
  • The VBA Match function VBA Find methods locate the desired column header name which is used as the Key argument in the VBA Sort method.
  • For sorting multiple columns, enter the Key and Order parameters for each column.

Download Practice Workbook

You can download the following practice workbook to practice yourself.


In short, sorting by column header name is a powerful and effective way to manage large sets of data. By entering the correct range and column header name, we can easily sort data in ascending or descending order. We can define worksheet events that automatically trigger the sorting process when double-clicking the column header. In addition, we can combine the VBA Match function and VBA Find method with the VBA Sort method to sort columns. We’ve also learned to sort multiple columns and sort by color. We are hopeful that this article has provided you with a clear understanding of how to sort by column header name in Excel VBA. Armed with this knowledge, readers can now confidently apply column-wise sorting to their own VBA projects. If you have any suggestions or comments, don’t forget to share them with us.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!


Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF