To sort an Excel range by two fields in VBA, add two sort keys in priority order, set the full range to sort, and apply the sort. For example, the macro below sorts rows A2:C100 first by column B, then by column C, while keeping row 1 as the header.
Option Explicit
Sub SortByTwoFields()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B100"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("C2:C100"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange ws.Range("A1:C100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Change the worksheet name, ranges, and ascending or descending options to match your data. The first key is the primary sort; the second key sorts rows only when the first field has matching values.
What sorting by two fields means
A two-field sort is a hierarchical sort across complete rows. Suppose a table lists department in column B, surname in column C, and an employee ID in column A. Sorting by department first groups employees by department. Sorting by surname second arranges people alphabetically within each department. The employee IDs move with their rows rather than being sorted independently.
The order of the sort keys matters. If surname is added first and department second, Excel sorts primarily by surname, then uses department to order people who share a surname. A secondary key does not override the primary key. It is used to resolve ties in the earlier key.
This approach works for two or more fields. Add another sort field before applying the sort for each additional level, such as department, then job title, then surname. Keep the sort range broad enough to include every column that belongs to each record.
How the VBA Sort object works
The worksheet’s Sort object holds the sort settings. Its SortFields collection holds the keys and their individual directions. The macro clears that collection before adding keys so old settings do not linger from an earlier sort. It then identifies the full range, declares whether the first row is a header, and calls Apply.
SortFields.Clearremoves previously configured keys.SortFields.Addadds one key and its options. The first call establishes the primary key; the next establishes the secondary key.SetRangespecifies all cells whose rows must move together, including the header in this example.Header = xlYestells Excel not to sort the top row as ordinary data.Orientation = xlTopToBottomsorts records down the rows, which is the usual layout for a table.Applyperforms the configured sort.
The key ranges in the example start at row 2 because the header is in row 1. They are still part of the range defined by SetRange. In general, the sort keys should be columns within the range being sorted. If a key is outside it, the sort can fail or produce an outcome that does not keep the intended records together.
Choose the correct range and header setting
Correct range selection is essential. If your records occupy columns A through F and rows 1 through 250, set the range to A1:F250, even if the two sort keys are only in columns B and D. Sorting just the key columns would separate those values from the rest of their records. A partial selection can make a spreadsheet appear corrupted because names, dates, or amounts no longer match their original rows.
Use xlYes when the first row contains field names, and xlNo when every row in the range is a record. If the header setting is omitted or guessed incorrectly, Excel may move a heading into the data or leave a data row fixed as though it were a heading. For a range with headers, include the header row in SetRange, but begin each key range at the first data row.
The example uses fixed end rows to make its assumptions explicit. Update both the sort range and each key range when your data grows or shrinks. If you sort regularly changing data, an Excel Table is often easier to manage: tables expand as rows are added, and their headers are recognized by Excel’s sorting tools. VBA code can also determine a last row dynamically, but the best column for that calculation depends on which column is reliably populated.
Set the direction for each field
Each key has its own Order setting. Use xlAscending for A to Z, smallest to largest, or earliest to latest; use xlDescending for the reverse. Mixed directions are allowed. For instance, you can sort by department ascending and then by hire date descending to show the newest hire first inside each department.
For that mixed sort, change only the second key’s argument to Order:=xlDescending. Leave the first key ascending. The order specified on one call to SortFields.Add does not set the order for the other key. When working with dates or numbers, ensure the cells contain genuine Excel date or numeric values rather than text that only looks like a date or number. Text values can sort differently from their apparent numeric meaning.
SortOn:=xlSortOnValues sorts by cell values, which is what most data-cleanup tasks need. Excel also supports sorting by cell color, font color, and conditional-format icon in appropriate contexts, but those options require their own settings. They are not necessary for a straightforward two-column value sort.
Run the macro safely in Excel
In Excel for Microsoft 365 on Windows, open the workbook in the desktop app, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module and paste the procedure into the module. Return to Excel and run the macro from the editor with the cursor inside the procedure, or use Developer > Macros, select SortByTwoFields, and choose Run. If the Developer tab is not visible, enable it in Excel Options under Customize Ribbon.
Excel for Mac also supports VBA macros. The Visual Basic Editor is available from Developer > Visual Basic; keyboard shortcuts can vary with the Mac keyboard and function-key settings. The macro here uses ordinary worksheet sorting and does not rely on Windows-specific components. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop version instead.
Save a copy of the workbook before running a macro. Sorting changes cell order, and macro actions cannot be undone with Ctrl+Z. Also confirm that macros are enabled only for a workbook you trust. If you need the macro to run again later, save the file in a macro-enabled format such as .xlsm; a standard .xlsx file does not retain VBA code.
Common problems and how to fix them
- The header gets sorted into the records: check that
.Header = xlYesis set and that the header row is included inSetRange. - Values no longer match the rest of their row: expand
SetRangeto cover every column in each record, not just the key columns. - The second field seems to have no effect: it only orders records tied on the first field. Use a primary column with repeated values to see the secondary order clearly.
- The macro says a sort key is invalid: check that each key range is on the same worksheet and falls within the sort range. Fully qualifying references with
ws.Range, as above, helps avoid using the active sheet by mistake. - Numbers or dates sort oddly: check for numbers stored as text, inconsistent date formats, leading spaces, or mixed data types in a column. Normalize the values before relying on the sort order.
- The wrong worksheet is sorted: confirm that the worksheet name in
ThisWorkbook.Worksheets("Data")exactly matches the sheet tab.ThisWorkbookmeans the workbook containing the macro.
Blank cells also affect how a sort looks. Excel places blanks according to its sort behavior, and a column with many blanks can make groups appear unexpectedly. If blanks have a specific business meaning, decide whether to leave them blank or fill them with a consistent value before sorting. Do not add placeholder text casually, since it will participate in the sort as an ordinary value.
When to use the ribbon instead of VBA
For a one-time sort, the Excel interface is often quicker. Select a cell in the data, then choose Data > Sort. In the Sort dialog, select the primary column and order, choose Add Level, then select the secondary column and order. Check that the dialog recognizes the header row before confirming. The dialog can also set sort options such as case sensitivity and, where appropriate, custom lists.
VBA is useful when the same sorting rule must be repeated, when a button or larger procedure should sort the data automatically, or when many workbooks need consistent processing. The interface is more convenient when the sort order changes often or a person needs to inspect the choices before applying them. A formula-based alternative such as the SORTBY function can return a sorted view without rearranging the source cells, but it requires a supported Excel version and is not a VBA command. Use a macro when the requirement is to sort the existing range in place.
Frequently Asked Questions
How do I sort by two columns in VBA?
Clear the worksheet’s SortFields, add the primary key first and the secondary key second, set the complete data range and header option, then call Apply. Each key has its own ascending or descending order.
Which sort field is the primary field?
The first field added to SortFields is the primary field. Excel sorts by it first, then uses the second field to order rows that tie on the primary value.
Can the two sort fields use different directions?
Yes. Set Order:=xlAscending or Order:=xlDescending separately in each SortFields.Add call. For example, sort a category ascending and dates within each category descending.
Do I include the header row in the key ranges?
No, not when the range has a header. Include the header row in SetRange, set .Header = xlYes, and start each key range at the first data row, as in B2:B100 and C2:C100.
Can I sort a range with more than two fields?
Yes. Add further keys to SortFields in priority order before applying the sort. Keep every key inside the complete range being sorted, and choose a direction for each one.
Can I undo a VBA sort with Ctrl+Z?
No. Excel clears its normal undo history when VBA changes the workbook, so Ctrl+Z will not reverse the macro’s sort. Save a separate copy first, or make a backup of the original data before running it.
Related
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range.Insert: Insert Cells, Rows, and Columns in ExcelIn VBA, use the Range.Insert method to add cells, rows, or columns and shift existing content out of…
- Easy Macro Recorder in Excel: How to Record and Use OneYes. Microsoft Excel for Microsoft 365 includes a built-in macro recorder: choose Developer > Record Macro, perform the…
- Excel Macros in Google Sheets: What Works?No: Google Sheets cannot run Excel VBA macros, even when you open an Excel workbook that contains them.…
- VBA Loop Types: For, Do, and While ExplainedVBA has four commonly encountered loop constructs: For…Next, For Each…Next, Do…Loop, and While…Wend. Use For…Next when a counter…
- VBA Find a Character in a StringIn VBA, use the InStr function to find a character in a string: it returns the character’s position,…
- Excel Macro Step Into: How to Debug VBA Line by LineIn Excel VBA, Step Into runs a macro one statement at a time and enters any procedure or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.