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

VBA Group Rows in Excel — Collapsible Outlines, Levels, and the Summary-Row Trap

|

VBA Group Rows in Excel — Collapsible Outlines, Levels, and the Summary-Row Trap

TL;DR — Rows("2:5").Group does not hide those rows and it does not delete them. It adds one band to a collapsible outline — the rows are still there, just foldable behind a +/− button in the margin. Grouping is a fold, not a hide. Two things trip everyone: the collapse button lands on the row below the group by default (Outline.SummaryRow), and you should collapse by level with ShowLevels, never by setting .Hidden yourself.

Sub GroupDetailRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Cells.ClearOutline                 ' wipe any old outline first, so re-running stays flat
    ws.Outline.SummaryRow = xlSummaryAbove ' put the +/- button on the total row ABOVE the detail
    ws.Rows("3:7").Group                   ' fold rows 3-7 under the total in row 2
    ws.Outline.ShowLevels RowLevels:=1     ' collapse to the top level in code, not by .Hidden
End Sub

Grouping rows, grouping columns, and the outline object itself are one system wearing three coats: .Group adds a band, the outline collapses and expands by level, and a single sheet setting decides which side the collapse button sits on. Get grouping rows right — the fold, the level, the summary side — and grouping columns is the same idea turned ninety degrees, driven by the same outline object.

What you'll learn

  • The mental model — grouping is a fold, not a hide and not a delete
  • The summary-row trap — why the +/− button lands below your group, and how to move it
  • Why you collapse by level with ShowLevels, never by setting .Hidden yourself
  • How nesting .Group deepens the outline level, and why re-running your macro over-groups
  • Ungroup versus ClearOutline — remove one band, or wipe the whole outline

The mental model: grouping is a fold, not a hide

Rows("2:5").Group is not "hide rows 2 to 5." It is "make rows 2 to 5 foldable." Excel draws an outline bar in the left margin with a − button; click it and the rows collapse out of sight; click the + and they come back. The rows were never hidden in the .Hidden = True sense — they are managed by an outline that knows how to fold and unfold them on demand.

This distinction is the whole article. A hide is a one-way state you have to remember to undo. A fold is a reversible view that Excel tracks for you, organised into levels, with buttons the reader can click. Everything that surprises people about grouping — where the button appears, why .Hidden and the outline disagree, why re-running the macro buries the data three levels deep — follows from grouping being a fold with structure, not a blunt hide.

The summary-row trap: which side the button lands on

Group rows 2 to 5 and the collapse button does not appear on the group — it appears on row 6, the row just below it. That is because Worksheet.Outline.SummaryRow defaults to xlSummaryBelow: Excel assumes your subtotal or total sits underneath the detail it summarises, so it parks the button there.

That default is right for a classic "detail then total" block. It is wrong — and it is the number-one grouping complaint — when your total sits above the detail, as it does on most dashboards:

ws.Outline.SummaryRow = xlSummaryAbove    ' button attaches to the total ABOVE the group
ws.Rows("3:7").Group                       ' total is row 2, detail is rows 3-7

Set it the other way and the button detaches from the number everyone actually reads: your total is in row 2, but the + is down on row 8, floating under the last detail row. SummaryRow is a sheet-level setting — decide it once, before you build the outline, to match where your totals live. Setting it after the fact re-flows the buttons for the sheet, but making it a deliberate first step is cleaner than being surprised.

Collapse by level, not by .Hidden

The tempting way to collapse a group in code is to hide the rows:

ws.Rows("3:7").Hidden = True               ' WRONG - a manual hide the outline does not track

Do not. That sets a hide the outline knows nothing about, so the +/− buttons and the actual row state drift apart — the button says expanded while the rows are hidden, or vice versa. Collapse through the outline instead, and Excel keeps everything consistent:

ws.Outline.ShowLevels RowLevels:=1         ' show only the top level - everything below folds away
ws.Outline.ShowLevels RowLevels:=2         ' show two levels deep - expand one layer back

ShowLevels drives the whole sheet to an outline level in one call, and the buttons stay truthful. If you need to expand or collapse a single node rather than a whole level, use ShowDetail on its summary row — covered with the rest of the outline object.

Levels: nesting groups, and why re-running over-groups

Group a range, then group a sub-range inside it, and you have not made two flat groups — you have made a nested outline, one level deeper:

ws.Rows("2:20").Group                       ' level 1 - the whole section
ws.Rows("5:9").Group                        ' level 2 - a sub-block inside it

An outline goes up to 8 levels deep, which is powerful for a real hierarchy and a trap when it happens by accident. The accident is re-running your macro: .Group on rows that are already grouped adds another band on top, so the second run leaves you at level 2, the third at level 3, with the data buried deeper each time. Grouping is not idempotent.

The fix is the same discipline as any "create it if it is not there" routine — reset before you rebuild:

ws.Cells.ClearOutline                       ' remove the entire outline, then build fresh
ws.Rows("3:7").Group

Clear first, group second, and the macro produces the same outline whether it runs once or ten times.

Ungroup versus ClearOutline

There are two ways to undo grouping, and they are not the same size:

ws.Rows("3:7").Ungroup                       ' removes ONE band / one level from that range
ws.Cells.ClearOutline                        ' removes the ENTIRE outline on the sheet, in one call

Ungroup is the precise tool — peel one level off one range. ClearOutline is the sledgehammer — wipe the whole outline and start clean. The mistake is looping Ungroup to clear everything: it is slower, it is fiddly to get the ranges right, and it is exactly what ClearOutline does in a single statement. Neither one deletes a single row of data — they remove the fold, not the rows.

How ExcelMaster helps

Grouping rows is one line that hides three decisions — which side the collapse button belongs on for your layout, whether to collapse by level or leak into .Hidden, and whether re-running quietly buries your data a level deeper each time — and getting any of them wrong fails without an error: a button floating away from its total, outline buttons that lie about the row state, or a report folded three levels too deep.

ExcelMaster lets you say what you want — "group the detail rows under each total, collapsed to the top level, button on the total row" — and it sets SummaryRow to match your layout, clears the old outline so a re-run stays flat, groups the right rows, and collapses with ShowLevels instead of .Hidden. You keep the workbook and the code.

Frequently asked questions

How do I group rows in VBA?

Call .Group on whole rows: Rows("3:7").Group folds rows 3 through 7 into one outline band with a collapse button in the left margin. Qualify the sheet — ThisWorkbook.Worksheets("Report").Rows("3:7").Group — so it does not depend on whichever sheet is active. The rows are not hidden or deleted; they become foldable.

Why is the group's collapse button below my rows instead of above?

Because Worksheet.Outline.SummaryRow defaults to xlSummaryBelow, which assumes your total sits under the detail. If your total is above the detail rows, set ws.Outline.SummaryRow = xlSummaryAbove before grouping so the +/− button attaches to the total row everyone reads.

How do I collapse or expand grouped rows in VBA?

Use the outline levels: ws.Outline.ShowLevels RowLevels:=1 collapses to the top level, and higher numbers expand more layers. Do not set .Hidden = True on the rows yourself — that is a manual hide the outline does not track, so the buttons and the real row state drift out of sync.

Why does my macro keep adding more groups every time it runs?

Because .Group on already-grouped rows adds another level on top — grouping is not idempotent. Call ws.Cells.ClearOutline at the start to wipe any existing outline, then group fresh, so the result is the same whether the macro runs once or ten times.

What is the difference between Ungroup and ClearOutline?

Ungroup removes one band or level from a specific range; ClearOutline removes the entire outline on the sheet in a single call. To undo one group use Rows("3:7").Ungroup; to clear everything use ws.Cells.ClearOutline rather than looping Ungroup. Neither deletes any data — they remove the fold.

Tested in

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

Related guides: VBA Group Columns · VBA Outline · VBA Hide Columns · VBA Insert Rows · VBA Delete Rows