VBA Sort Sheet: How to Sort Excel Data with a Macro

VBA & macros · Updated November 18, 2026

To sort a sheet with VBA, define the complete data range, add the column or columns to sort by, and apply the worksheet’s Sort object. The macro below sorts columns A:D on a worksheet named Data by column B, while keeping each row’s values together and treating row 1 as the header.

What sorting a sheet with VBA means

Excel does not need a special command to sort an entire worksheet. A macro sorts a specified range of cells on a worksheet. That distinction matters: if a record uses several columns, sorting only one column can separate a name from its address, date, or amount. The range passed to SetRange should therefore contain every column that belongs to the records being sorted.

A sort key is the column Excel uses to decide the order of the rows. For example, a sales list might be sorted by the date in column B, while the full records span columns A through D. The key tells Excel what to compare; the sort range tells Excel which cells move together. A sort can be ascending or descending, and it can use more than one key to break ties.

The examples here target desktop Excel for Microsoft 365 on Windows. VBA macros also work in supported desktop versions of Excel for Mac, though the route to the VBA editor and some keyboard shortcuts differ. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.

A basic VBA macro to sort a range

This example assumes that the worksheet is named Data, row 1 contains headers, column A is filled for each record, and the data occupies columns A through D. It finds the last used row by looking up column A, then sorts the records by column B from smallest to largest. For dates, ascending order means earliest to latest.

Option Explicit

Sub SortDataByColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Use the named sheet in this workbook, not whichever sheet is active.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Row 1 is the header; there is nothing to sort if it is the only row.
    If lastRow < 2 Then Exit Sub

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

In the code block, the HTML-escaped ampersand in the range expression represents the ordinary VBA concatenation operator. In an actual VBA module, enter & as the ampersand character: the key range expression should read "B2:B" & lastRow. Excel uses the header setting to keep row 1 out of the sort, even though the overall sort range includes it. The key starts at B2 so the header is not treated as a value to sort.

The range is intentionally explicit. If the records actually extend through column F, change both occurrences of the sort range’s last column from D to F. If column A is not reliably filled, choose another column that has a value in every record when calculating lastRow; otherwise the macro may leave lower records unsorted.

Change the sort column and direction

To sort on another field, change the key range. For example, change "B2:B" & lastRow to "C2:C" & lastRow to use column C as the primary key. The key must remain inside the overall range being sorted. The field can contain text, dates, or numbers, but values that look alike may sort differently if some are stored as text and others as real dates or numbers.

To reverse the direction, replace xlAscending with xlDescending. Text then sorts in reverse alphabetical order, numbers from largest to smallest, and dates from latest to earliest. This changes the order only; it does not convert text dates into actual Excel dates or otherwise repair inconsistent source data.

  • Use xlAscending for A to Z, low to high, or oldest to newest.
  • Use xlDescending for Z to A, high to low, or newest to oldest.
  • Keep .Header = xlYes when the first row contains field names. Use xlNo only when the range has no header row.

Sort by more than one column

Multiple keys are useful when the primary field contains repeated values. For instance, sorting first by region and then by date groups records by region and orders the dates inside each region. Add the keys in priority order: the first key is primary, and later keys resolve ties. The following complete example assumes columns A:D contain records, with region in A and dates in B.

Option Explicit

Sub SortByRegionThenDate()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then Exit Sub

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

Here, rows are grouped by region in ascending order; within each region, dates are sorted from newest to oldest. If the required priority is date first and region second, reverse the order of the two .SortFields.Add statements. The keys do not have to be adjacent, but each must be a column in the range being sorted.

Run the macro safely

Save a copy of the workbook before running a sorting macro. Sorting changes the order of the underlying cells, and VBA actions cannot be undone with Ctrl+Z. A backup is especially important if the workbook contains formulas, manually arranged sections, or data that has not been backed up elsewhere.

  1. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code.
  2. On Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, then paste the macro into the module.
  3. Change the worksheet name, key column, and range boundaries to match the workbook. Confirm that the complete records fit within the chosen range.
  4. Return to Excel and run the macro from Developer > Macros, or run it from the Visual Basic Editor. On Mac, use the Developer tab’s Visual Basic command if available; keyboard shortcuts can depend on the keyboard and function-key settings.

Excel may display a security notice when a workbook contains macros. Run code only when you trust its source; do not lower macro security settings indiscriminately to make an unfamiliar file run. If the Developer tab is hidden, it can be enabled in Excel Options on Windows or Excel Settings on Mac. These ribbon and settings names can vary slightly with platform and release.

Common range and header mistakes

The most damaging mistake is sorting the key column alone. If names are in A and phone numbers in B, sorting only A can leave the original phone numbers in place beside different names. Set the sort range to include all columns that make up a record, including any related notes or identifiers.

Another common problem is choosing the wrong last-row column. The sample uses column A because it assumes that every record has a value there. If A contains gaps, Excel’s End(xlUp) method may find a row above the bottom of the actual dataset. Use a consistently populated column, or calculate the extent of the data using a method suited to the workbook’s layout. A fully blank row in the middle can also make a simple range-based process harder to define; decide whether that row is a real separator or an accidental gap before sorting.

  • Header moved into the data: check that the header setting is xlYes and that the range begins on the header row.
  • Some columns did not move: expand .SetRange to include all fields belonging to each record.
  • Dates or numbers appear out of order: check whether values are stored consistently as actual dates or numbers rather than text.
  • Rows outside the intended list moved: make the range precise instead of sorting a broad area that includes unrelated content.
  • Unexpected order for text: consider capitalization, leading spaces, and the chosen sort order. The sample does not distinguish letter case.

Also avoid including totals or explanatory notes beneath the records unless they belong in the sort. A macro that treats a total row as data may move it into the middle of the list. In a structured report, it is often safer to identify the actual data region or use an Excel Table rather than assume that every used cell on the sheet is part of one sortable list.

Sorting an Excel Table instead

If the data is an Excel Table, using the table’s own data range can be more maintainable than hard-coding A:D. Tables expand when new records are added and provide named columns, so a macro can refer to a table and its fields rather than fixed column letters. This is useful for recurring lists whose size changes over time.

For example, a table can be sorted through its ListObject.Sort property. The table name and field names must match the workbook, and the chosen sort key should be a column in that table. A table sort also keeps each table row intact. It does not automatically mean that unrelated cells outside the table should be included; those cells remain separate from the table’s records.

For a one-time sort, the standard Excel interface may be simpler: select a cell in the data and choose Data > Sort, then specify the field and order. A macro is most helpful when the same sorting procedure must be repeated, triggered by a button, or included in a larger workflow. For an existing Excel Table, the filter arrows in its header can also sort interactively without writing code.

Frequently Asked Questions

How do I sort an entire sheet by one column in VBA?
Choose the full rectangular range containing the records with .SetRange, add the desired column as a sort key, and apply the sort. Do not select just the key column if other columns in each row must stay attached. A worksheet may contain unrelated sections, so “entire sheet” should not automatically mean every used cell.

How do I sort a column from Z to A in VBA?
Set the key’s Order argument to xlDescending. Keep the sort range wide enough to include every field in each record, and set the header option correctly so the column heading is not sorted as data.

Can VBA sort a sheet by two columns?
Yes. Clear the existing sort fields, then add one .SortFields.Add statement for each key in priority order before applying the sort. The first key controls the main grouping; the second determines order among rows tied on the first key.

Can I run a VBA sort in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA. The web version can still support other sorting methods available in its interface, but that is separate from executing a VBA procedure.

Why did my header row get sorted?
The sort may be configured with .Header = xlNo, or the selected range may start on the wrong row. Include the header in the overall range and set .Header = xlYes when the first row is a heading. If there is no heading, use xlNo and make sure the first data row is included in the key range.

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.