Sort Using VBA in Excel: Code Examples, Options, and Tips

Yes. In Excel for Microsoft 365 on Windows, you can sort data with VBA by defining the full range, specifying a sort key and order, and applying the worksheet’s Sort object. The example below sorts a three-column table by its values in column B, largest to smallest, while keeping each row’s data together.

What sorting with VBA means

Sorting rearranges rows according to values in one or more columns. A VBA sort performs the same basic job as the commands on Excel’s Data tab, but a macro can repeat the operation with the same settings whenever you run it. That is useful for recurring reports, imported data, or a workbook where readers need a consistent one-click process.

The most important rule is to sort the entire record range, not just the column used as the key. If you sort only the sales figures in column B, for example, those numbers can become detached from the customer names and dates in the other columns. A properly defined sort range moves each whole row as a unit.

VBA sorting is not the same as the SORT worksheet function. A macro changes the order of cells in the source range. A formula such as SORT, available in Microsoft 365 and Excel 2021 or later, returns a sorted result in another area and leaves the original data in place.

A basic VBA sort example

Before running a macro, save a copy of the workbook. VBA changes to cell values or order cannot be undone with Ctrl+Z, so a backup is the safest way to recover if the macro sorts the wrong range. The following procedure assumes a worksheet named Data, headers in row 1, names or other records in column A, and the sort values in column B.

Sub SortDataBySales()
    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 used row in the consistently populated key column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No data rows to sort.", vbInformation
        Exit Sub
    End If

    ' Include all three columns so each row stays together.
    Set sortRange = ws.Range("A1:C" & lastRow)

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

In this example, column B is sorted in descending order, so higher values appear first. The sort range includes columns A through C, and the header setting tells Excel that row 1 is labels rather than a record to rearrange. Change the sheet name, range columns, key column, and order to match your workbook.

How the Sort object works

The code uses Excel’s Sort object, which lets you define the sort range and one or more sort fields before applying the operation. Clearing SortFields at the start avoids accidentally carrying over old sort keys saved on the worksheet. The key range identifies the values Excel compares; it is only one column in the example, while SetRange identifies every cell whose rows should move.

Several settings are worth understanding:

Key: The cells that determine the order. The example uses B2 through the last data row, deliberately leaving out the header.
Order: Use xlAscending for A to Z, oldest to newest, or smallest to largest; use xlDescending for the reverse.
Header: xlYes means the first row in the sort range contains headings. Use xlNo when the range has no header row.
Orientation: xlTopToBottom sorts records by rows, the usual arrangement for a table. A left-to-right sort is a different operation.
MatchCase: False means uppercase and lowercase are not treated as separate ordering categories.

Explicitly setting these options makes a macro easier to maintain than relying on whichever sort settings happened to be selected previously in the workbook.

Sort by more than one column

For a multi-level sort, add a sort field for each key, in priority order. For example, a list of orders might first be sorted by region and then by order date, so dates are ordered within each region. In the example procedure, replace the single SortFields.Add line with two entries like these, using the appropriate key columns and range endpoints:

.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:=xlAscending, DataOption:=xlSortNormal

With the first key in column A and the second in column C, Excel sorts by A first and uses C to order rows that share the same A value. Keep both key ranges aligned with the same rows in SetRange. For a third level, add another field before applying the sort. If you need a custom order such as High, Medium, Low rather than alphabetical order, define a custom list or add a helper column that assigns each category a numeric rank; a rank column is often easier to inspect and update.

Choose and calculate the range carefully

The sample macro finds the last row by looking upward in column A. This works when every record has a value in A, but it can miss records if the last rows in that column are blank. Choose a column that is reliably populated, such as a unique ID, or calculate the last used row using a different suitable method for your layout. A worksheet with unrelated content elsewhere also needs a deliberately chosen data range; simply sorting the entire used area can include notes or other tables.

The example’s A:C range is intentionally clear, but it assumes the data always occupies those columns. If columns are added, update the range so every field belonging to a record is included. Blank rows inside a range may be sorted among the records, and merged cells can prevent a normal sort. For an Excel table, using the table’s defined data area can make the intent clearer and helps the range expand as table rows are added. Avoid including totals or footnotes unless they are meant to be sorted as records.

Install and run the macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Code > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon and select the Developer check box. In the Visual Basic Editor, choose Insert > Module, paste the procedure into the module, and change the worksheet name and column references before running it.

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) so it can retain VBA code.
Return to Excel and choose Developer > Macros.
Select SortDataBySales and choose Run. Check the resulting order and confirm that each row’s fields stayed together.

Excel for Mac desktop also supports VBA, although its menus and ribbon setup differ from Windows. Excel for the web does not run, create, or edit VBA macros; use the desktop app to run this code. If a workbook opens with macros disabled, only enable them when you trust the file and its source. Do not lower macro security settings globally just to run an unfamiliar workbook.

Common problems and practical checks

A macro that runs without an obvious error can still produce the wrong result if its assumptions do not match the sheet. Check these issues when a sort looks unexpected:

Wrong worksheet: The tab name must match the text in Worksheets exactly. ThisWorkbook means the workbook containing the macro, not necessarily the workbook currently selected on screen. Keep the macro with the data or deliberately reference the intended workbook.
Incomplete rows: Confirm that SetRange covers every field in each record and that the key range has the same first and last data rows.
Header moved into the data: Set Header to xlYes only when the first row of SetRange really contains headings. With xlNo, Excel sorts that row as data.
Numbers or dates sort oddly: Values stored as text may sort differently from numeric values or real Excel dates. Check the cell values and formats, and convert inconsistent entries before sorting.
Only part of the list changes: Recheck the final-row calculation, especially if the chosen anchor column contains blanks near the bottom. Also check for blank rows or separate blocks of data.
Macro cannot be run: Confirm the file is saved as .xlsm, the procedure is in a standard module, and Excel has permitted macros from this trusted file.

Test changes on a copy with a few recognizable rows, including ties and blank cells, before using the macro on an important report. This helps reveal range mistakes that are not obvious when the data is large.

VBA sorting compared with other options

Use the Data tab’s Sort command when you need a one-time sort or want to choose options interactively. It is visible and does not require enabling macros. Use VBA when the same precise sort must be repeated, triggered from a button, or combined with other workbook steps.

Use a SORT formula when you want a separate, automatically updated sorted view and need to preserve the source order. In Microsoft 365 or Excel 2021 and later, SORT can return a sorted array without rearranging the source cells. The formula approach has different behavior from a macro: it needs space for the result to spill and does not itself change the original table. Choose based on whether you want to rearrange the source or display a derived view.

Frequently Asked Questions

Can VBA sort a range in ascending order?
Yes. Set the relevant SortFields.Add Order argument to xlAscending. Use xlDescending when you want the largest or latest values first.

How do I sort without moving the header?
Include the header row in SetRange and set .Header = xlYes. The key range should begin on the first data row below the header, as in B2:B followed by the last data row.

Can a VBA macro sort by multiple columns?
Yes. Clear the sort fields, add one SortFields entry per key in priority order, set the full range, and apply the sort. Excel uses later keys to break ties in earlier keys.

Why does my macro sort only some rows?
A common reason is that the last-row calculation relies on a column with blanks at the bottom. Use a consistently populated anchor column or calculate the data range to fit the worksheet, and verify that the full record range is included.

Can I undo a VBA sort with Ctrl+Z?
No. Excel’s undo history is cleared when VBA changes the workbook. Save a separate copy before running a sorting macro, particularly when the original order matters.

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.