Excel like a finance pro.
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 SubPaste 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
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
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
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
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
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.
| 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.