To sort Excel rows by multiple columns with VBA, use the worksheet’s Sort object, add one sort field for each key in priority order, set the full data range, and apply the sort. The example below sorts a sheet named Data by Department (A–Z), then Date (newest first), then Employee (A–Z).
Example VBA macro
This version assumes row 1 contains headers, Employee is in column A, Department in B, Date in C, and another field such as Amount in D. It finds the last used row by looking in column A, so each data row should have an employee value there.
Sub SortDataByMultipleColumns()
Dim ws As Worksheet
Dim lastRow As Long
Dim sortRange As Range
' The worksheet must be named Data.
Set ws = ThisWorkbook.Worksheets("Data")
' Assumes every data row has an employee value in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then Exit Sub
Set sortRange = ws.Range("A1:D" & lastRow)
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange sortRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub
Change the worksheet name, range, and key columns to fit your workbook. Save a copy before running any macro: VBA changes cannot be reversed with Ctrl+Z.
How multi-column sorting works
A multi-key sort applies the first key to the whole range, then uses the next key to order rows that tie on the first key. In this example, all rows are grouped by Department. Within each department, dates are ordered from newest to oldest. If two rows have the same department and date, Employee names determine their order. The third key does not override the first two; it resolves ties left by them.
The sequence of calls to .SortFields.Add establishes that priority. Put the most important key first. To sort by Amount descending within each department, for example, add the Amount range after Department and set its order to xlDescending. You can add more fields the same way, provided each key belongs to the sort range.
The macro sorts entire rows from A through D, not just the key columns. That matters: sorting only column B would separate departments from their associated dates, names, and amounts. Always set .SetRange to cover every column whose values must stay together.
Adjusting the macro for your worksheet
Start by mapping the example’s columns to your actual layout. If your table runs from A through F, expand the sort range to F and select key ranges from the appropriate columns. For a key in column E, for example, use E2 through the last data row. Keep the key ranges on the same worksheet as the sort range, and exclude the header row from those key ranges when .Header is set to xlYes.
The example uses the last nonempty cell in column A to find the data boundary. If column A can be blank on the final data row, this method may leave that row out. Choose a column that is reliably populated, or calculate the last row using another dependable field. If records have gaps or different columns end at different rows, define the data boundary deliberately rather than assuming one column represents every record.
For data that starts below row 1, update both the range and the key starting rows. For instance, if headers are on row 4, set the sort range to start at row 4, the sort keys to start at row 5, and retain xlYes for the header setting. A worksheet with no header row needs a different range start and xlNo.
Headers, dates, and data types
The .Header = xlYes setting tells Excel the first row of the range is a header, so it stays at the top instead of being sorted as a record. Use xlNo only when the range truly has no headers. xlGuess asks Excel to infer the setting, but an explicit choice is more predictable in a repeatable macro.
Excel sorts values according to their underlying data types, not just how they look on screen. A date displayed as 11/02/2026 may be a real date serial or text that resembles a date. Real dates sort chronologically; text dates can sort alphabetically or inconsistently, especially when formats differ. If the date order looks wrong, check whether the cells contain actual dates and whether values are stored consistently.
Numbers stored as text can also sort unexpectedly relative to numeric values. Likewise, leading or trailing spaces can affect text ordering. Clean or convert inconsistent data before sorting if the order matters. The example uses xlSortNormal, which does not request special text-as-number handling.
Running and saving the macro
- Save a backup copy of the workbook. Then save the working file as an Excel Macro-Enabled Workbook (
.xlsm) if it contains VBA. - Open the Visual Basic Editor. On Windows, press Alt+F11. In Excel for Mac, use Option+F11; some keyboards require Fn+Option+F11.
- In the editor, choose Insert > Module and paste the procedure into the module.
- Return to Excel and run the macro with Alt+F8 on Windows, select
SortDataByMultipleColumns, and choose Run. On Mac, the macro dialog shortcut is commonly Option+F8, though keyboard settings can affect function-key behavior.
Excel may show a security notification when a workbook contains macros. Only enable macros in files from sources you trust. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop version of Excel for these steps. The VBA editor and shortcuts differ on Mac, but the sort code itself is generally usable in desktop Excel for Mac.
Sorting an Excel table instead of a fixed range
A fixed range such as A1:D100 is straightforward, but it must be updated when records are added. The example avoids a fixed last row by finding it in column A, though that still depends on the assumption that column A is populated. For an expanding dataset, formatting the data as an Excel table with Insert > Table can make the data boundary easier to manage: tables expand as rows are added and include their headers as part of the table structure.
Table sorting is also available through the filter arrows in the header row, and Excel’s Data tab provides sort controls for ordinary ranges. Those interface options are useful for a one-time sort; VBA is more suitable when the same multi-column order must be applied repeatedly. If you adapt a macro to sort a table, target the table’s data range and table sort fields rather than accidentally sorting unrelated cells beside it.
Common problems and checks
- Wrong sheet name: If the workbook has no worksheet named Data, the line assigning
wswill fail. Replace the name with the exact worksheet tab name. - Rows become mismatched: The sort range is too narrow. Expand it to include every column belonging to each record.
- The header moves: Check that the range begins at the header row and that
.Headeris set correctly. - Some final records are missing: The last-row column may be blank in those records. Use a consistently filled column or another boundary method.
- Unexpected date or number order: Check for text values, inconsistent formats, spaces, or mixed data types.
- Sort does not run: Merged cells within the range, protected-sheet restrictions, or an invalid key range can prevent the operation. Confirm the sheet can be edited and each key is inside the selected data range.
For a quick check, inspect a few rows at each boundary and verify that all fields still belong to the same record. Also confirm the priority: the first key should separate the major groups, with later keys ordering records only within those groups.
VBA sorting versus Excel’s Sort command
Excel’s Data > Sort command is often the simplest choice when a person is sorting a range occasionally. It lets you choose columns, sort direction, and whether the data has headers without writing code. It also prompts users to expand a selection when adjacent data might belong to the same records, a helpful safeguard for manual work.
VBA is useful when the same process must run from a button, as part of a larger procedure, or on a regular schedule initiated by a user. The trade-off is maintenance: code depends on worksheet names, column positions, headers, and assumptions about blanks. Document those assumptions and update the ranges whenever the workbook layout changes. Neither method is inherently more accurate; a correct range and correct key order matter in both.
Frequently Asked Questions
How do I sort by three columns in Excel VBA?
Clear existing sort fields, add three key ranges in priority order with .SortFields.Add, set the full record range with .SetRange, identify whether it has headers, and call .Apply. The example above sorts by Department, Date, then Employee.
Does the first sort column have the highest priority?
Yes. The first field is the primary key. Later fields order records that are tied on earlier keys, so add the most important grouping or ordering column first.
Can I sort one column without moving the rest of the row?
You can, but it is usually unsafe for records: names, dates, and other related fields would no longer stay together. Set the sort range to include the complete rows of data unless the column is intentionally independent.
How do I sort in descending order?
Set that key’s Order argument to xlDescending. Use xlAscending for ascending order. Each field can have its own direction, as the date key does in the sample.
Can I undo a VBA sort with Ctrl+Z?
No. Running a VBA macro clears Excel’s ordinary undo history, so Ctrl+Z will not restore the previous order. Save a copy first, or make the macro preserve a separate copy of the original data if the workflow requires recovery.
Will this macro work in Excel for the web?
No. Excel for the web does not run VBA. Open the macro-enabled workbook in desktop Excel for Windows or Mac to use the Visual Basic Editor and execute the procedure.
Related
- Data Validation Testing: Methods and Examples
- Data Validation Studio: Excel Rules, Setup, and Limits
- Power Query Circular Reference: Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Dynamic List: Populate and UpdateTo create a dynamic list in an Excel VBA UserForm, clear a ListBox or ComboBox and refill it…
- VBA InputBox Default: How to Set It and Handle CancelIn VBA, set an InputBox default by supplying the third argument: InputBox(prompt, title, default). That value appears in…
- Excel VBA Range.Formula: How to Read and Set FormulasIn Excel VBA, use a range’s Formula property to read or write a worksheet formula in A1 notation.…
- VBA MsgBox If: Check a User’s Choice with an If StatementIn VBA, use MsgBox inside an If statement to show a prompt and run different code according to…
- Excel VBA Input Box Dropdown List: Options and SetupExcel’s built-in VBA InputBox does not support a dropdown list. To let someone choose from a visible list,…
- VBA Loop Through a Range: Examples and Best PracticesTo loop through cells in Excel VBA, use For Each cell In worksheet.Range("A1:A10").Cells and place the action you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.