Wauvel

Excel like a finance pro.

← All VBA snippets

Sort a range in code, on more than one column

Department A to Z, then amount largest first — and the header argument that ruins it.

1What it does

The Sort object takes as many keys as you add: department ascending, then amount descending within each department. What makes it worth writing carefully is that a sort is one of the few operations in Excel that can corrupt data with no visible sign. Sort a range that's missing a column and the rows come apart — every department paired with the wrong customer, and nothing on screen looks wrong. Always sort the whole contiguous block, and tell it explicitly whether the first row is a header.

2The code

Sub SortByDeptThenAmount()
    Dim ws As Worksheet, rng As Range, lastRow As Long, lastCol As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    ' The WHOLE block, every column — never one column on its own.
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    With ws.Sort
        .SortFields.Clear                         ' old keys persist otherwise
        .SortFields.Add2 Key:=ws.Range("A2:A" & lastRow), _
                         Order:=xlAscending        ' department A-Z
        .SortFields.Add2 Key:=ws.Range("C2:C" & lastRow), _
                         Order:=xlDescending       ' then amount, largest first
        .SetRange rng
        .Header = xlYes                           ' NEVER leave this as xlGuess
        .Apply
    End With
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Group a transaction list by department, biggest amounts first.
  • Order an aging report by days overdue within each customer.
  • Put a list into a predictable order before a lookup or a subtotal.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

Dept · Customer
Sales · Acme$4,200
Marketing · Globex$9,800
Sales · Umbrella$6,300
Marketing · Initech$2,750

Two ways to sort this. One of them corrupts it with no visible sign.

5Pitfalls

Sorting one column on its own scrambles every row, silently.

Fix: SetRange on the entire contiguous block, every column, as above.

`.Header = xlGuess` sometimes decides the header is data and sorts it into the middle of the list.

Fix: xlYes or xlNo, explicitly. Never let it guess.

The sort uses keys from last time as well as the new ones.

Fix: .SortFields.Clear first — the Sort object remembers.

Numbers stored as text sort alphabetically: 100 before 9.

Fix: Convert the column to real numbers first. The sort is right; the data isn't.

6No-code alternatives

  • Data → Sort dialog Identical, by hand, with Add Level for each key. See the tip.
  • SORT / SORTBY Return a sorted copy and leave the source untouched, which removes the corruption risk entirely.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.