Auto-Grouping Rows by Title Hierarchy in VBA

Overview

Excel VBA Outlining

Manually grouping and ungrouping rows in Excel is tedious, especially in large financial models with multiple heading levels. These VBA macros automate outline grouping based on which column contains each row's title text, supporting 2 to 8 hierarchy levels.

The approach is elegant: select a range where column position indicates hierarchy depth (column A = Level 1, column B = Level 2, etc.), and the macro automatically creates the correct outline grouping. Collapse_All and Expand_All provide quick toggle control across all sheets.

How It Works

Title Detection: The macro checks which column contains text for each row. Column A content marks a top-level heading, column B marks a sub-heading, and so on
Group Then Ungroup: First groups all selected rows to maximum depth, then selectively ungroups title rows to their correct level based on column position
Parent Row Fix: A second pass corrects parent rows that should not be grouped under their children, ensuring the outline hierarchy is logically consistent
Collapse/Expand All: Companion macros that set all sheets to outline level 1 (collapsed) or level 8 (fully expanded) in one click

The VBA Code

Sub Collapse_All()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    Next ws
    Range("A1").Select
End Sub

Sub Expand_All()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
    Next ws
    Range("A1").Select
End Sub

Sub Group_Title_3Level()
    Dim i As Integer, j As Integer, k As Integer
    Dim Rng As Range, cell As Range

    ' Group all rows in selection to max depth
    For i = 1 To Selection.Columns.Count
        Selection.Rows.Group
    Next i
    Set Rng = Selection.Resize(Selection.Rows.Count, 1)

    ' Ungroup rows based on title hierarchy
    For Each cell In Rng
        If Not IsEmpty(cell.Value) Then
            ' Level 1: content in first column
            j = Selection.Columns.Count
            cell.Rows.Ungroup
        ElseIf Not IsEmpty(cell.Offset(0, 1).Value) Then
            ' Level 2: content in second column
            j = Selection.Columns.Count - 1
            cell.Rows.Ungroup
        End If
        For k = 1 To j - 1
            cell.Rows.Ungroup
        Next k
    Next cell
    
    ' Fix parent rows that shouldn't be grouped
    For Each cell In Rng
        If cell.EntireRow.OutlineLevel = 3 Then
            If Not IsEmpty(cell.Offset(1, 0).Value) Or _
               Not IsEmpty(cell.Offset(2, 0).Value) Then
                cell.Rows.Ungroup
            End If
        End If
    Next cell
End Sub

Final Thoughts

This tool is invaluable for financial models and reports where section headings follow a column-based indentation pattern. Instead of manually grouping hundreds of rows, select the range and let the macro build the entire outline hierarchy in seconds.