VBA Code to Sort Excel Data by One or More Columns

VBA & macros · Updated November 18, 2026

To sort Excel rows with VBA, define the complete data range, add one or more sort keys, set the header option, and apply the sort. The macro below sorts a worksheet’s used rows by column B in ascending order, then by column C in ascending order.

Ready-to-use VBA code

This example assumes a worksheet named Data, headers in row 1, and records in columns A through C. It finds the last row by looking up column A, so that column should contain a value for every record.

Sub SortDataByTwoColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last record using column A as the anchor column.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub ' Nothing to sort below the header.

    Set sortRange = ws.Range("A1:C" & lastRow)

    With ws.Sort
        .SortFields.Clear
        ' The first field is the primary sort; the second breaks ties.
        .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:=xlAscending, DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The macro sorts the rows together, not just the values in column B or C. That distinction matters: sorting a single column by itself can detach names, dates, or other details from their associated records.

How Excel’s VBA sort works

Excel’s sort object describes the operation before applying it. The code clears any old sort fields, adds the columns to sort by, identifies the full block of data, and applies the settings. .SetRange is particularly important: it tells Excel which columns must move as a unit when rows are reordered.

  • .SortFields.Clear removes sort keys left over from a previous sort operation on that object.
  • .SortFields.Add defines a key column and its order. xlAscending sorts from low to high, A to Z, or oldest to newest, depending on the data.
  • .Header = xlYes tells Excel that the first row contains labels, not records to be sorted.
  • .Orientation = xlTopToBottom sorts records vertically, which is the usual arrangement for a table.
  • .Apply performs the sort. Until that line runs, the settings alone do not rearrange the data.

The sort key ranges start at row 2 because row 1 is the header. The overall sort range starts at row 1 so the header remains part of the sorted block and stays at the top. This is a common pattern for a normal worksheet range with one header row.

Choose the correct range and header setting

Change "Data" to the exact worksheet tab name, and adjust "A1:C" to cover every column that belongs to each record. For example, if a customer list uses columns A through F, set the range to ws.Range("A1:F" & lastRow). Include all related fields even if the sort key is in only one of them.

The code uses column A to find the last row. That works when column A is reliably populated for every record. If another column is a better anchor, change both occurrences of the column letter in the last-row expression. For example, to use column B, use ws.Cells(ws.Rows.Count, "B").End(xlUp).Row. A blank cell in the middle of the anchor column does not stop this method from finding the last nonblank cell, but a final record with a blank anchor value may not be included.

Set .Header according to the actual range:

  • Use xlYes when the first row contains column names.
  • Use xlNo when every row in the range is data.
  • Use xlGuess only when you deliberately want Excel to infer whether a header exists. For repeatable macros, an explicit setting is usually clearer.

If the list begins on a different row, revise the range and sort-key start rows consistently. For example, if headers are on row 4 and records begin on row 5, the sort range should begin at row 4 and each key range at row 5.

Sort by one column, multiple columns, or descending order

For a single-key sort, keep just one .SortFields.Add line and remove the second one. To sort descending, change that key’s Order argument from xlAscending to xlDescending. These constants can be used for dates, numbers, and text; Excel orders the values according to their data types and sort rules.

When there is more than one key, the first key added is the primary sort. In the sample, Excel groups or orders records by column B first. When two rows have the same value in B, column C determines their order. You can add another sort field for a third level, such as a date after department and surname. Every key must fall within the overall range being sorted.

For example, a sales sheet might use region as the primary key and sales amount as the secondary key. To put the largest sales amounts first within each region, set the region key to xlAscending and the sales key to xlDescending. A secondary key does not reorder records across different primary-key groups; it resolves order among records tied on the earlier key.

Run the macro safely in Excel

  1. Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
  2. Open the workbook in the Excel desktop app. On Windows, press Alt+F11 to open the Visual Basic Editor.
  3. In the editor, choose Insert > Module, then paste the code into the module.
  4. Change the sheet name and range assumptions if needed. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
  5. Return to Excel and press Alt+F8, select SortDataByTwoColumns, and choose Run.

On Mac, use the Visual Basic Editor from Excel’s Developer tools or the Tools > Macro menu if available in the installed version. Keyboard shortcuts can depend on the Mac keyboard and function-key settings, so the menu is a dependable alternative. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for this code.

Only enable macros in workbooks you trust. Excel’s security prompts are there to help prevent unsafe code from running. A sorting macro should not require you to lower macro security globally. If the workbook is from an unknown source, do not enable its macros just to inspect the data.

Common sorting problems and fixes

  • The macro says the worksheet subscript is out of range. The sheet name in the code does not exactly match a tab in the workbook, or the sheet is in a different workbook. Check spelling and confirm which workbook ThisWorkbook refers to.
  • Headers move into the records. Confirm that the sort range begins with the header row and that .Header = xlYes. If there is no header, use xlNo.
  • Only one column appears sorted. Make sure .SetRange covers every column in each record. Do not sort the key column alone if the other columns need to stay attached to it.
  • The last few records are missing. The last-row calculation may use a column that is blank in those records. Choose an anchor column that is populated through the final row, or use another reliable way to define the data boundary.
  • Numbers or dates sort unexpectedly. Some cells may contain numbers or dates stored as text, or the column may mix types. Check the cell values and formats, and make the data consistent before sorting.
  • The macro runs but does not sort the expected block. Verify the worksheet, range endpoints, key columns, and header row. Also check that the key ranges and sort range refer to the same sheet.

Blank rows inside a list can also make it harder for Excel to identify a contiguous data region, especially when the range is being selected automatically. The sample defines a rectangular range explicitly, but it still assumes that the rows inside that rectangle belong to the list. Remove unintended blank rows or define the data boundaries more carefully if the sheet contains separate blocks.

Sorting Excel tables and changing the approach

If the data is formatted as an Excel table, the table already tracks its data rows as records and expands when new rows are added. You can still sort the table with VBA, but make sure the range or table reference includes every table column. A fixed range such as A1:C100 may omit newly added rows; a last-row calculation is more adaptable when the table grows. For a table with a consistent name, VBA can refer to its ListObject and its data range, which is useful when the worksheet has other content nearby. The specific table name is available under Table Design > Table Name.

VBA is useful when sorting is part of a repeated workflow, such as a button that standardizes a report. For a one-time sort, use Excel’s Data > Sort command: select a cell in the data, open Sort, choose the column and order, and confirm whether the range has headers. That approach avoids writing or enabling a macro.

For a separate, automatically updating sorted result, Microsoft 365 and Excel 2021 also have the worksheet SORT function. With the source records in A2:C100 and the desired sort key in the second column of that range, use =SORT(A2:C100,2,1) in an empty cell. It returns the rows in ascending order by that second column without rearranging the source. This formula spills into neighboring cells, so those cells must be clear. Unlike a VBA sort, it creates a sorted view rather than changing the original record order.

Frequently Asked Questions

How do I sort a column in VBA without separating the rows?
Set the sort range to the full block of related columns, not just the key column. Excel will then move each row as a unit while using the selected column to determine the order.

Can I sort from Z to A with VBA?
Yes. In the relevant .SortFields.Add line, use Order:=xlDescending instead of Order:=xlAscending. The descending option also reverses numeric and date order.

Can I sort a range with no header row?
Yes. Set .Header = xlNo and make sure the sort range begins with the first data row. If the range has headers but Excel is told there are none, the header can be sorted as if it were a record.

Why can’t I undo a VBA sort with Ctrl+Z?
Excel clears its normal undo history when VBA changes the workbook. Save a copy before running the macro if you may need to restore the original order; Ctrl+Z will not reverse the macro’s sort.

Can I run VBA sorting in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app to run the macro, or sort the data using the web app’s available sorting commands.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.