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.

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

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:

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.

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

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.

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

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

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

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

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

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

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.

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

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

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.

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")
wsht.Sort.SortFields.Clear
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
error_handling:
'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

The video below shows all the steps described so far.

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

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.

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, _
SearchFormat:=False).Activate
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
error_handling:
'handles invalid column name and other errors
If Err.Number = 91 Then
MsgBox "The column header does not exist", vbOKOnly
Else
MsgBox Err.Number & ": " & Err.Description
End If
End Sub

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

5. Sorting Multiple Columns by Header Names

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

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.

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

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

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

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.

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

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.

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.Clear
.SortFields.Add(Key:=sort_col, Order:=xlAscending, _
SortOn:=xlSortOnCellColor).SortOnValue.Color = RGB(189, 215, 238)
.Header = xlYes
.Apply
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.

Conclusion

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

• Excel VBA Sort Named Range
• Excel Macro: Sort Multiple Columns with Dynamic Range
• Excel VBA Sort Range with Multiple Keys