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.
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.
Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
|Key||Optional||Specifies the range or the column header to be sorted.|
|Order||Optional||Specifies the order of the sorting.
|Type||Optional||Specifies the PivotTable elements to be sorted.|
|Header||Optional||Specifies whether the first row contains headers or not.
|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.
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
- 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
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
- 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
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
- 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
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
- 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
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.
- 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.