Excel VBA Sort: How to Sort Data with a Macro

VBA & macros · Updated November 21, 2026

To sort a worksheet range with Excel VBA, define the full range, add one or more sort keys, set whether the range has a header, and apply the sort. For example, the macro below sorts a table on the Sales sheet by column B in ascending order while keeping each row’s values together.

What sorting with VBA does

VBA sorting automates the same basic task as sorting from Excel’s ribbon: it reorders rows according to values in one or more columns. It is useful when a workbook needs a repeatable sort, when a macro already prepares a report, or when a button should sort a list consistently. VBA does not sort only the chosen column in isolation; the range passed to the sort must include all the columns whose row data should stay together.

In current desktop Excel for Microsoft 365 on Windows, the Sort object is a clear way to specify the sort range and keys. Its SortFields collection holds the sort instructions, while properties such as Header and Orientation describe how Excel should interpret the range. The example uses xlAscending, so text is ordered A to Z and numbers from low to high. Use xlDescending for the reverse order.

A sort changes row order in the workbook. It is not the same as filtering, which hides rows without rearranging them, or the worksheet function SORT, which returns a sorted result in a separate spill range in supported Excel versions. VBA is most useful when the desired action is to rearrange the original records or run the same operation as part of a larger macro.

A basic Excel VBA sort macro

This example assumes the sheet is named Sales, row 1 contains column headings, column A has a value on every data row, and column B contains the dates or other values to sort. It finds the last used row from column A and the last used column from the header row, then sorts the complete rectangle.

Sub SortSalesByColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sortRange As Range

    ' Identify the worksheet and the table boundaries.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' Stop if there are no data rows below the header.
    If lastRow < 2 Then Exit Sub

    Set sortRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

The key starts at B2 because row 1 is a heading; the entire sort range starts at A1 so the heading is included and identified with .Header = xlYes. If your worksheet name, header row, or key column differs, change those references together. The macro uses ThisWorkbook, meaning the workbook that contains the code, rather than whichever workbook happens to be active.

Choose a range that keeps records intact

The range passed to .SetRange is one of the most important parts of a reliable sort. If a record spans columns A through F, sort A:F together. Sorting just the values in column B could separate dates or names from the rest of their row and corrupt the table’s meaning. Likewise, include every relevant row, including the header if you set Header to xlYes.

The sample detects the bottom row by looking up column A. That works when column A is populated for every record. If it can contain blanks, choose a column that is always filled, such as a record ID, or use a different method to find the actual table boundary. The last-column check looks across row 1, so it assumes the headings occupy that row. A title or blank row above the headings needs a corresponding adjustment to the range and key.

For a structured Excel table, you can use its table range rather than infer boundaries from worksheet cells. This can be helpful when rows are added over time, provided the table’s name is stable. Do not assume CurrentRegion is always appropriate: it expands to the contiguous block around a cell and may stop at a completely blank row or column, or include nearby data that is not part of the intended list.

Sort by two or more keys

Multiple keys define a priority order. For example, sorting first by department and then by employee name groups departments while ordering names within each group. Add the more important key first and the tie-breaker afterward. In the following complete example, column A is sorted A to Z, then column C is sorted largest to smallest within matching values in A. It assumes the same Sales sheet layout and a populated column A.

Sub SortSalesByDepartmentAndAmount()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim sortRange As Range

    ' Find the table boundaries.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Then Exit Sub
    Set sortRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A2:A" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add Key:=ws.Range("C2:C" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

For three levels, add another .SortFields.Add statement before .SetRange. Make sure each key range lines up with the same data rows and falls inside the overall sort range. If a key column contains numbers stored as text, Excel may order them differently than genuine numeric values; correcting the source data is usually better than relying on sort options to disguise inconsistent data.

Headers, text, blanks, and sort direction

Set .Header = xlYes when the first row of the selected range contains headings. Set it to xlNo when the range contains only records. Using the wrong setting can move a heading into the data or treat the first record as a heading. Excel can sometimes guess whether a range has headers, but an explicit setting is more predictable in a macro.

The sample sorts top to bottom, which is the usual choice for a list. .Orientation = xlTopToBottom makes that direction explicit. Use a key range that excludes the heading row when Header is yes. To sort by values in descending order, change that key’s Order argument to xlDescending; in a multi-key sort, each key can have its own direction.

Sorting dates works best when the cells contain actual Excel date values rather than date-looking text. Similarly, numbers stored as text, inconsistent spaces, and mixed formats can produce surprising-looking order. Blank cells and duplicate keys are allowed; where duplicate rows end up relative to one another may not be meaningful unless you add another key to define a tie-breaker.

Common VBA sort problems

  • “Subscript out of range”: The worksheet name in Worksheets("Sales") must match the tab name exactly. Check spelling and spaces.
  • Only one column moves: Expand the sort range to cover the entire record, not just the key column.
  • The heading gets sorted: Confirm the range begins at the header and that .Header = xlYes is set. If there is no heading, use xlNo.
  • Some rows are missed: The last-row detection may be based on a column with blanks, or the data may extend beyond the header row’s last used column. Choose reliable boundary columns or use a table’s range.
  • The order looks wrong: Check for text-formatted numbers, dates stored as text, leading spaces, or a key range that starts on the wrong row.
  • The macro affects another sheet: Qualify ranges with the worksheet variable, as the examples do, rather than relying on whichever sheet is active.

When investigating, check the intended range and key addresses before changing sort settings. A macro can complete without an error while still sorting the wrong cells, so verify a few representative rows and confirm that each record remains intact.

Run and save the macro safely

On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and paste the procedure. Run it from the editor with the cursor inside the procedure, or use Alt+F8 to open the Macro dialog and select its name. The Developer tab can also provide access to Visual Basic and Macros. If that tab is hidden, enable it in Excel Options under Customize Ribbon.

Save a macro-enabled workbook as .xlsm if it contains the VBA code. Excel for the web can open a workbook with macros, but it cannot create, edit, or run VBA macros; use the desktop app. Excel for Mac supports VBA, though shortcuts can depend on the keyboard and function-key settings; use the Visual Basic Editor and Macro commands from the Mac menus or ribbon if a shortcut differs. The worksheet sorting code shown here does not rely on Windows-specific file paths or controls.

Save a separate copy of the workbook before running a macro on important data. VBA actions generally cannot be reversed with Ctrl+Z, including a sort, so a backup is the safest way to recover the original order. Enable macros only for workbooks you trust and follow your organization’s macro security rules.

When to use VBA instead of another sort method

Use the Data tab’s sort commands for a one-time sort that a person will inspect and perform manually. They are visible, require no macro-enabled file, and make it straightforward to choose levels and options. A VBA macro is a better fit when the same sort must happen repeatedly, should run as part of a report workflow, or needs to be triggered by another procedure.

A worksheet formula may be preferable when you want a separate, automatically updated sorted view while preserving the source order. The SORT function is available in Microsoft 365 and Excel 2021 or later, but it returns results rather than rearranging the source records. Choose based on the desired outcome: a permanent change to row order, a repeatable macro action, or a live sorted copy. For a simple one-off task, VBA adds setup and macro-security considerations that may not be worthwhile.

Frequently Asked Questions

How do I sort an Excel range in VBA?
Set the full data range, add a key with SortFields.Add, specify whether there is a header, and call .Apply. Use a worksheet-qualified range so the macro acts on the intended sheet.

How do I sort a column but keep rows together?
Use the key column to determine order, but set the sort range to include every column belonging to each record. Sorting only one column can disconnect its values from the rest of the row.

Can VBA sort by multiple columns?
Yes. Add a separate SortFields.Add entry for each key before applying the sort. The first key sets the primary order; later keys resolve ties within earlier keys.

Can I run an Excel VBA sort in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to edit or run the macro.

Can I undo a VBA sort with Ctrl+Z?
Usually not. Running VBA clears Excel’s normal undo history, so save a copy before sorting if you may need the original row order.

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.