🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Sort in Excel — Range.Sort vs the Sort Object (and How to Get the Original Order Back)

|

VBA Sort in Excel — Range.Sort vs the Sort Object (and How to Get the Original Order Back)

TL;DR — Sorting in code is a permanent reorder: there is no Ctrl+Z after a macro runs, so once .Sort fires, the original row order is gone unless you saved it. If you might need it back, add an index column (1, 2, 3…) before sorting so you can sort back later. Always pass Header:=xlYes or Excel may sort your title row into the data. And sort a complete record — the whole table via CurrentRegion, never a single column, or you reorder one column and scramble the rest.

' Sort a table by column C (3rd col in the range) descending - the whole record moves together.
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sales")
With ws.Range("A1").CurrentRegion               ' whole table, so every column stays aligned
    .Sort Key1:=.Columns(3), Order1:=xlDescending, Header:=xlYes
End With

Sorting looks like the safest operation in Excel — you do it by hand a hundred times a day and undo it with a keystroke. In code, that safety net is gone. The single most important thing to understand about VBA sorting is not the syntax; it is that a sort in a macro is a mutation you cannot casually reverse. Everything below is organised around protecting your data from that fact, then the mechanics.

What you'll learn

  • The mental model — a sort is a permanent mutation, and how to make it reversible
  • The one rule that saves your data — sort a complete record, never a lone column
  • Header:=xlYes — why omitting it drops your titles into the data
  • Range.Sort (quick, 3-key limit) vs the Sort object (verbose, unlimited, persistent)
  • The SortFields.Clear trap — inheriting sort keys from a previous run
  • Why numbers stored as text sort as 1, 10, 2 — and how to fix it

The mental model: a sort is a mutation, not a view

The previous guide framed AutoFilter as a view — reversible, hiding nothing permanently. Sort is the opposite: a mutation. It physically rewrites the order of the rows. There is no hidden "original" underneath to restore. And critically, Application.Undo does not work after a macro sorts — running VBA clears Excel's undo stack, so the user cannot press Ctrl+Z either.

That changes how you should think before every sort: if this order matters, I have to preserve it myself. The cheap, bulletproof way is an index column — stamp the rows 1, 2, 3… before you sort, so "sort back to original" is just a sort on that column:

' Add a restore-order column BEFORE sorting.
Dim i As Long, lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
    ws.Cells(i, "Z").Value = i - 1        ' 1, 2, 3... in a spare column
Next i
' ...now sort however you like; to restore, sort by column Z ascending.

Adopt that habit for any sort whose original order you might need, and the "there is no undo" problem disappears.

The rule that matters most: sort the whole record, not one column

This is the mistake that silently destroys data. If you select one column and sort it, Excel reorders that column only and leaves every other column where it was. Names now line up with the wrong phone numbers; invoice IDs point at the wrong amounts. Nothing errors — the sheet just becomes quietly, permanently wrong.

' WRONG - sorts column B alone, tearing it away from the rest of each row.
ws.Range("B2:B100").Sort Key1:=ws.Range("B2"), Order1:=xlAscending

' RIGHT - sort the whole table; every column travels with its row.
ws.Range("A1").CurrentRegion.Sort Key1:=ws.Range("B2"), Order1:=xlAscending, Header:=xlYes

The rule: sort the smallest range that is still a complete record. In practice that means the whole table — Range("A1").CurrentRegion grabs the contiguous block for you. The sort key can be a single column; the sort range must span every column of the record. This is the exact same alignment lesson as deleting a single cell instead of a whole row — reorder part of a record and you corrupt all of it.

Header:=xlYes or your titles get sorted into the data

Range.Sort has a Header argument, and its default is xlGuess — Excel tries to detect whether the first row is a header. When it guesses wrong, your column titles get sorted down into the data like any other row, landing somewhere in the middle. Never let it guess:

.Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes   ' first row is titles - leave it put

Pass Header:=xlYes when your range includes the header row (the usual case), xlNo when it is pure data. This one argument prevents the classic "my headers ended up in row 43" bug.

Two ways to sort: Range.Sort vs the Sort object

VBA gives you two APIs, and they trade brevity for power.

Range.Sort is the quick one — a single statement with up to three sort keys (Key1/Key2/Key3). Perfect for the common case:

' Sort by Region (asc), then Amount (desc) within each region.
ws.Range("A1").CurrentRegion.Sort _
    Key1:=ws.Range("B1"), Order1:=xlAscending, _
    Key2:=ws.Range("C1"), Order2:=xlDescending, _
    Header:=xlYes

The Worksheet.Sort object is the verbose one — you add SortFields one at a time — but it lifts the three-key ceiling and supports sorting by cell color, font color, and icon, which Range.Sort cannot. It also persists: the sort settings stick to the sheet, which is exactly why it has a sharp edge.

With ws.Sort
    .SortFields.Clear                       ' <-- CRITICAL: drop leftover keys first
    .SortFields.Add Key:=ws.Range("B2:B100"), Order:=xlAscending
    .SortFields.Add Key:=ws.Range("C2:C100"), Order:=xlDescending
    .SetRange ws.Range("A1").CurrentRegion
    .Header = xlYes
    .Apply
End With

The trap that defines the Sort object: SortFields.Clear must come first. Because the sort fields persist on the worksheet, whatever keys a previous macro run — or a user sorting by hand — left behind are still there. Skip .Clear and you sort by the old keys plus your new ones, in a mix you never asked for. My rule: reach for Range.Sort for everything up to three keys, and only move to the Sort object when you genuinely need more keys or a color/icon sort — and when you do, SortFields.Clear is the first line inside the With.

The data-type trap: numbers stored as text

A sort that "comes out in the wrong order" is almost always a data-type problem. If a column of numbers is actually stored as text — common after importing from CSV or the web — Excel sorts it lexically, character by character: "1", "10", "2", "21", "3". The values look like numbers and sort like words.

The fix is upstream: convert the column to real numbers before sorting, so 2 sorts before 10. That is a conversion job — see CStr, CDate and Val for turning imported text into genuine numeric types. Once the column holds real numbers, the sort is correct; no amount of sort-argument tweaking fixes text pretending to be numbers.

How ExcelMaster helps

Sorting hides its dangers behind a familiar, friendly operation: the missing Header:=xlYes that buries your titles, the single-column sort that scrambles records, the absent SortFields.Clear that inherits stale keys, the text-as-numbers order, and above all the fact that there is no undo once a macro has run. None of these raise an error — they just leave your data reordered wrongly and unrecoverable.

ExcelMaster lets you describe the sort you want. Say "sort by region, then by amount highest first" and it sorts the whole record (not one column), sets Header explicitly, clears stale sort fields, and — because it backs the sheet up first — leaves you a way back even though Excel's own undo is gone. You keep the workbook and the code; you skip the part where a one-column sort quietly detaches every name from its number.

Frequently asked questions

Can I undo a sort done by a VBA macro?

No. Running a macro clears Excel's undo stack, so neither your code nor the user can Ctrl+Z a sort a macro performed. Protect the original order yourself before sorting — the simplest way is to stamp an index column (1, 2, 3…) so you can sort back to it later, or back up the sheet first.

Why did my VBA sort scramble the data?

Almost always because you sorted a single column instead of the whole record. Sorting one column reorders only that column and leaves the others in place, so each row's values no longer belong together. Sort the entire table — Range("A1").CurrentRegion — with the sort key pointing at the column you want ordered.

How do I stop VBA from sorting my header row into the data?

Pass Header:=xlYes to .Sort (or set .Header = xlYes on the Sort object). The default is xlGuess, which lets Excel try to detect the header and sometimes sorts your titles down into the rows. Setting xlYes explicitly keeps the first row fixed as the header.

What is the difference between Range.Sort and the Sort object in VBA?

Range.Sort is a single statement limited to three sort keys — quick and right for most jobs. The Worksheet.Sort object adds keys one at a time with SortFields.Add, supports unlimited keys and sorting by color or icon, and persists its settings on the sheet — so you must call SortFields.Clear first to avoid inheriting old keys.

Why does my VBA sort put 10 before 2?

Because those numbers are stored as text, and text sorts lexically ("1", "10", "2") rather than numerically. Convert the column to real numbers before sorting — see the CStr/Val conversion guide — and the values will sort in true numeric order.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-08.

Related guides: VBA Find · VBA AutoFilter · VBA CStr, CDate and Val · VBA Delete Rows · VBA Range