VBA Method to Sort Data in Excel

VBA & macros · Updated November 18, 2026

The VBA method to sort data in Excel is the Sort object, configured with a sort key, a range, and a header setting, then applied with .Apply. For a straightforward one-column sort, you can also use Range.Sort; the examples below show how to sort complete rows safely, add secondary keys, and adapt the code to changing data.

How VBA sorting works

A sort rearranges records according to values in one or more columns. The important point is to sort the entire rectangular data range, not just the column that determines the order. If names are in column A and dates are in column B, sorting only column B would separate dates from their names. Set the full table or range as the sort range, and use the relevant column as the key.

Excel VBA offers two common approaches. Range.Sort is compact and convenient for a simple sort. The worksheet’s Sort object is more explicit: you clear any existing sort fields, add one or more keys, set the complete range and header behavior, and apply the sort. The latter is usually easier to extend when the rules become more involved.

A sort key is the column or range Excel examines to decide the order. Ascending order puts text alphabetically and numbers from low to high; descending reverses the order. Dates sort chronologically when Excel recognizes the cells as dates. A value that merely looks like a date but is stored as text may not sort as expected.

A basic macro for a single sort key

This example assumes a worksheet named Data, headers in row 1, and records in columns A through D. It sorts the records by column B in ascending order. Column A is used to find the last occupied row, so choose a column that is consistently populated in your own data.

Option Explicit

Sub SortDataByColumnB()
    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("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

The key begins at B2 because row 1 contains headings, while .SetRange includes row 1 so the headings remain associated with their columns. .Header = xlYes tells Excel to treat that first row as a header rather than a record. The If check prevents a sort attempt when there is no data row below the header.

To sort the same range from largest to smallest, change xlAscending to xlDescending. For text, ascending generally means A to Z. If you omit or misidentify the header setting, Excel can treat a heading as a data value and move it into the records.

What the main sort settings mean

In the example, .SortFields.Clear removes sort fields left on that worksheet’s sort object from a previous operation. Clearing them before adding new keys helps prevent an old criterion from unexpectedly affecting the result. SortOn:=xlSortOnValues sorts by cell values, which is the usual choice for a data list.

DataOption:=xlSortNormal uses Excel’s normal handling of values. .MatchCase = False means the sort does not distinguish uppercase from lowercase as separate case-sensitive values. .Orientation = xlTopToBottom sorts records down the rows, which is the normal arrangement for a list. A horizontal sort across columns is a different operation and should be configured deliberately rather than used for a standard record table.

Use a clearly defined range. In the sample, A:D is intentional: it ensures all four columns move together. If your dataset has additional columns, expand that range to include them. A sort does not automatically know that unrelated cells beside the list belong to the same records.

Sorting by more than one column

For a secondary sort, add another field after the primary one. For example, the following macro sorts by department in column C first, then by employee name in column B within each department. The ranges again assume row 1 is the heading row and column A is populated for every record.

Option Explicit

Sub SortByDepartmentThenName()
    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("C2:C" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .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

The first field added is the primary criterion; the next field breaks ties in that order. You can add further fields in the same way, provided each key lies inside the range being sorted. To reverse only the name order, set the second field’s order to xlDescending and leave the department field ascending.

Handling changing row counts

Hard-coding a range such as A1:D100 is simple, but it becomes unreliable when records are added or removed. The example macros calculate a last row before sorting, so the range grows with the data. The chosen anchor column matters: if column A contains gaps near the bottom, End(xlUp) may stop at an earlier row and leave later records out. Use a column that is filled on every record, or calculate the last row using a different reliable column.

If the data has a consistent header and is likely to grow, an Excel Table is often a better foundation than a manually maintained rectangular range. Tables expand when new rows are added and include named columns, which makes it easier to identify the intended data. When adapting a table-based macro, use its data body and column names rather than assuming fixed worksheet coordinates; be careful not to include unrelated cells around the table.

Blank rows within a list can also make the intended boundary unclear to a person reviewing the sheet, even when a calculated range includes them. A clean, continuous table is easier to sort, filter, and maintain. If blank rows are meaningful, decide explicitly whether they should be included and where they should appear.

Common problems and how to avoid them

  • Only the key column moves: Set the sort range to all columns that make up each record, not just the key.
  • The header is sorted as data: Confirm that the range includes the header and set .Header = xlYes. Use xlNo only when the range genuinely has no header.
  • Some records are missing: Check the last-row calculation and verify that its anchor column is populated through the final record.
  • Dates or numbers appear out of order: Check whether the cells contain actual numeric or date values rather than text that only looks numeric or date-like.
  • An earlier criterion still affects the sort: Clear existing fields with .SortFields.Clear before adding the criteria for the current run.
  • The macro reports an error: Confirm the worksheet name, range addresses, and that every key is within the range being sorted.

Sorting merged cells can also cause problems because merged layouts do not behave like ordinary rectangular records. For reliable tabular sorting, avoid merged cells in the data range. Keep headings in one row, use one record per row, and do not place notes or totals inside the range unless they are intended to be sorted with the records.

Running the macro safely

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the macro into the module. Return to Excel and run it from Developer > Macros, or use Alt+F8 to open the Macro dialog. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. On Mac, use Tools > Macro > Visual Basic Editor if the keyboard shortcut differs on your keyboard.

Save a copy of the workbook before running a sorting macro. VBA actions cannot be undone with Ctrl+Z, so an incorrect range can be difficult to reverse reliably. Also save macro-enabled workbooks in a format that supports VBA, such as .xlsm; saving as a macro-free workbook format can remove the code. Only enable macros in workbooks you trust.

For debugging, place the cursor inside the procedure and step through it in the Visual Basic Editor, checking the calculated lastRow and the range passed to .SetRange. A quick check of those two values often reveals why the macro sorted too few rows or included unintended cells.

When to use VBA instead of a built-in sort

For a one-time sort, the Data tab’s sort controls are usually quicker and avoid maintaining code. VBA is useful when the same sort must be repeated, triggered by another macro, or applied consistently as part of a routine. It can also reduce manual steps when a workbook contains several similarly structured sheets.

For a separate, automatically ordered result that updates from source data, Excel’s SORT worksheet function may be an alternative in Microsoft 365 and Excel 2021 or later. Unlike a VBA sort, a formula returns sorted results in another area rather than rearranging the original records. Choose based on whether you want to reorder the source list or display a sorted view.

Frequently Asked Questions

What is the simplest VBA command to sort a range?
For a basic case, use Range.Sort with a key, sort order, and header setting. For more control—especially multiple keys—the worksheet’s Sort object makes each part of the sort explicit.

How do I sort in descending order in VBA?
Set the relevant sort field’s Order argument to xlDescending instead of xlAscending. In a multi-key sort, set the order separately for each field.

Does VBA sort the whole row automatically?
It sorts the range you specify. Include every column belonging to each record in .SetRange; otherwise, cells outside that range will not move along with the key column.

How can I sort without moving the header?
Include the header row in the sort range and set .Header = xlYes. The key ranges should start below the header, as in B2:B followed by the last data row.

Can I sort an Excel Table with VBA?
Yes. A table has its own sort object and named columns, which can be useful when rows are added over time. Use the table’s actual columns and data body when setting sort keys, and avoid relying on fixed row numbers if the table size changes.

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.