How to Sort by Column Header Name Using VBA in Excel (5 Ways)

The video overviews this article, representing sorting 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, and more. This is our starting point in the world of VBA.

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

This launches the Microsoft Visual Basic for Applications window. Click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code. Press the Run button or F5 key to execute the code.

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:

Method 1 – Sorting a Single Column by Header Name

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

1.1 Ascending Order

To apply this VBA code, follow the steps shown previously to insert a Module.

• 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. The unsorted and sorted data are shown side-by-side.

The arguments in this code are virtually identical; we must change the Order1 argument to xlDescending (descending order). Since this code is similar to the previous method, please 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

Method 2 – Sorting by Double-Clicking Column Header Name

You can easily sort the data using VBA code 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.

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. Double-clicking the Company or Revenue (Billion) column headers activates the event and sorts the data.

Method 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 columns 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.

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

Another alternative to sorting data by column header is to use the VBA Find and Sort methods. The program sorts the “Year Founded” column in ascending order.

This code is remarkably similar to the previous example and can 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

See the video, which recaps all the steps to run this code.

Method 5 – Sorting Multiple Columns by Header Names

I assume the Employee Information dataset is in the B4:D14 cells, which contain the employee’s 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.

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

See 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 a Header in Excel VBA

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

We’ll set the starting range and use the VBA Sort method. One exception is that the Header argument is set to show 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 also sort by color with Excel VBA. In the above image, we’ll sort the “Revenue (Billions)” column such that the blue cells are shown before the green and gold cells.

We’ll create an Excel Table using the CTRL + T shortcut keys.

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.

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.

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 seek is not in the given range.
• Change the Worksheet and Table names when using the VBA code.
• When sorting data in VBA, use the Order parameter to determine the proper sort order, which can be set to either xlAscending or xlDescending.
• Worksheet events run a specific event on that worksheet; therefore, choose the correct event.

Download the Practice Workbook

You can download the following workbook to practice.

Related Articles

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