VBA Outline in Excel — Drive Collapse Levels, AutoOutline, and Subtotals in Code

TL;DR — The outline is the object that every group builds on. Once it exists, you drive the whole sheet through it: ws.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1 collapses or expands every group to a level in one call — far better than looping .Hidden. Excel can also build the outline for you: AutoOutline reads your summary formulas, and Subtotal inserts the totals, the groups, and the outline all at once.

Sub CollapseToTotals()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1   ' both axes to a level in one call
End Sub

Grouping rows and grouping columns both add bands to this object. Learning to drive the outline directly — collapse by level, let Excel auto-build it from formulas, generate it with subtotals, and reset it cleanly — is what turns a pile of .Group calls into a report you can fold, unfold, and rebuild on demand.

What you'll learn

ShowLevels — collapse or expand the whole sheet to a level, both axes in one call
AutoOutline — let Excel build the outline from your formulas, and why it fails without them
Subtotal — auto-create totals, groups, and outline together, and the re-run trap that stacks them
ShowDetail — expand or collapse a single node instead of a whole level
ClearOutline versus RemoveSubtotal — remove the fold, or remove the fold and the inserted rows

ShowLevels: collapse the whole sheet by level

The outline organises groups into levels — level 1 is the most collapsed (totals only), higher numbers show more detail. ShowLevels sets the sheet to a level directly, without touching a single .Hidden:

ws.Outline.ShowLevels RowLevels:=1                    ' rows: show only the top level (totals)
ws.Outline.ShowLevels RowLevels:=3                    ' rows: expand to three levels deep
ws.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1  ' set both axes at once

Rows and columns are independent axes, so you can pass one and leave the other alone. Two things worth knowing: ShowLevels with no arguments does nothing — you must pass at least one axis — and a level higher than the deepest group simply fully expands that axis, it does not error. This is the tool that replaces every hand-rolled loop of .Hidden = True/False, and it keeps the outline buttons honest.

AutoOutline: let Excel build it — if your formulas earn it

AutoOutline builds an outline automatically by reading your formulas. If column N totals columns B through M with =SUM(B2:M2), and a subtotal row adds up the rows above it, Excel infers the structure and draws the groups for you:

ws.UsedRange.AutoOutline                     ' infer groups from the summary formulas

The catch — and the reason people file it as broken — is that it is not magic. AutoOutline needs consistent summary formulas to read. Point it at a plain block of values with no SUM/SUBTOTAL structure and it fails with run-time error 1004, "Cannot create an outline":

On Error Resume Next
ws.UsedRange.AutoOutline
If Err.Number <> 0 Then MsgBox "No summary formulas to outline"   ' expected on unstructured data
On Error GoTo 0

So AutoOutline is the right call on a formula-structured report and the wrong call on a flat data dump. When the data has no totals yet, the tool that adds them is Subtotal.

Subtotal: totals, groups, and outline in one call

Range.Subtotal is the feature that does the whole job at once — it inserts subtotal rows with SUBTOTAL(9, ...) formulas at each change of a key column, groups the detail beneath them, and builds the outline over the lot:

ws.Range("A1:D200").Subtotal _
    GroupBy:=1, _                            ' subtotal at each change in column 1
    Function:=xlSum, _
    TotalList:=Array(4)                      ' sum column 4

The number-one Subtotal bug is re-running it. A second call does not replace the first — it stacks a new set of subtotals inside the existing ones, nesting the outline a level deeper each time, exactly like re-running .Group. Remove before you rebuild:

ws.Range("A1:D200").RemoveSubtotal           ' strip the inserted rows AND the outline first
ws.Range("A1:D200").Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4)
' - or - pass Replace:=True to overwrite an existing set in one call

ShowDetail: expand or collapse a single node

ShowLevels moves the whole sheet; ShowDetail moves one group. Set it on a summary row or column to expand or collapse just that node:

ws.Rows(6).ShowDetail = True                 ' expand the group whose summary is row 6
ws.Rows(6).ShowDetail = False                ' collapse just that one group

Use ShowLevels when you want a uniform view of the whole report, and ShowDetail when you want to open one section — a monthly block, one department — while the rest stays folded.

ClearOutline versus RemoveSubtotal

Two resets exist, and the difference is what they take with them:

ClearOutline removes the fold and nothing else. The rows, the subtotal rows, the formulas — all stay; only the grouping structure disappears. ws.Cells.ClearOutline wipes it sheet-wide.
RemoveSubtotal removes the fold and the inserted subtotal rows, returning the range to the raw data you started with.

Pick by intent. If you want to keep the totals but drop the collapsibility, ClearOutline. If you want the data back exactly as it was before you subtotaled it, RemoveSubtotal. Reaching for ClearOutline when you meant to undo a subtotal leaves the inserted total rows stranded in your data with no outline to organise them.

How ExcelMaster helps

The outline is a small object with four verbs that are easy to mix up — ShowLevels moves the whole sheet, ShowDetail moves one node, AutoOutline only works when formulas back it, and Subtotal quietly stacks on a re-run unless you remove first — and choosing the wrong one fails without an error: an AutoOutline that throws 1004 on flat data, doubled subtotals nested a level too deep, or a ClearOutline that strips the fold but strands the total rows.

ExcelMaster lets you say what you want — "subtotal the sales by region, collapse to the totals, and don't double them if I run it again" — and it removes the previous subtotals before rebuilding, collapses with ShowLevels, uses AutoOutline only where formulas support it, and picks ClearOutline or RemoveSubtotal to match whether you want to keep the totals. You keep the workbook and the code.

Frequently asked questions

How do I collapse or expand an outline in VBA?

Use ShowLevels: ws.Outline.ShowLevels RowLevels:=1 collapses rows to the top level (totals only), and higher numbers expand more detail. Add ColumnLevels to drive the column axis in the same call. It sets the whole sheet at once and keeps the outline buttons in sync, unlike setting .Hidden by hand.

Why does AutoOutline give error 1004, "Cannot create an outline"?

Because AutoOutline builds the outline by reading your summary formulas, and there are none for it to read — the range is flat values with no SUM/SUBTOTAL structure. Add summary formulas or subtotals first, or use Range.Subtotal to insert them. AutoOutline is for formula-structured reports, not raw data dumps.

Why do my subtotals double when I run the macro twice?

Because Subtotal stacks a new set inside the existing one rather than replacing it. Call Range.RemoveSubtotal before re-subtotaling, or pass Replace:=True on the Subtotal call, so each run produces one clean set instead of nesting them a level deeper.

What is the difference between ClearOutline and RemoveSubtotal?

ClearOutline removes only the grouping structure and leaves every row — including subtotal rows — in place. RemoveSubtotal removes the outline and the inserted subtotal rows, returning the range to the original data. Use ClearOutline to drop just the collapsibility, RemoveSubtotal to undo a subtotal entirely.

How do I expand just one group instead of the whole outline?

Set ShowDetail on that group's summary row or column: ws.Rows(6).ShowDetail = True expands the group whose summary is row 6, and = False collapses it, while every other group stays as it was. Use ShowLevels for a uniform whole-sheet view and ShowDetail for one section at a time.

Tested in

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