I commonly use sheets that look like the following:

That is built as a comparison tool based on exports from another application. Note that the rows are merged 2 at a time.

The number in left-most column is currently being used for conditional formatting to add the coloring, but it also represents the hierarchy that I am trying to group by.

As it currently stands, I go through and manually group the rows so that they can be expanded and collapsed. My goal is to figure out a way to use VBA to do that work for me since these sheets can often be 5,000+ rows.

I've found similar threads that get me close, but I haven't figured out a way to make it work with merged rows like shown, the closest I have been able to get is grouping just the first row but not grabbing both.

Any help is greatly appreciated! Thanks!

I commonly use sheets that look like the following:

That is built as a comparison tool based on exports from another application. Note that the rows are merged 2 at a time.

The number in left-most column is currently being used for conditional formatting to add the coloring, but it also represents the hierarchy that I am trying to group by.

As it currently stands, I go through and manually group the rows so that they can be expanded and collapsed. My goal is to figure out a way to use VBA to do that work for me since these sheets can often be 5,000+ rows.

I've found similar threads that get me close, but I haven't figured out a way to make it work with merged rows like shown, the closest I have been able to get is grouping just the first row but not grabbing both.

Any help is greatly appreciated! Thanks!

• excel
• vba

• 1 Have you tried recording a macro while grouping a subset of the rows, then looking at the generated code? That might give you a useful starting point. – Tim Williams Commented Mar 31 at 23:12

2 Answers 2

I'm not a real user of grouping in Excel, so I might be misunderstanding your use case, but this seemed to work for me on a simple example:

Sub GroupIt()
    Dim ws As Worksheet, c As Range, grp As Range
    Dim rngLevels As Range, lvl As Long, maxLvl As Long, i As Long
    
    Set ws = ActiveSheet
    ws.Cells.ClearOutline               'clear previous grouping
    Set rngLevels = ws.Range("A2:A33")  'range with the level values
    maxLvl = Application.Max(rngLevels) 'max level value
    Debug.Print maxLvl
    Application.ScreenUpdating = False
    For lvl = maxLvl To 2 Step -1       'loop down from max. level
        Set grp = Nothing
        For Each c In rngLevels.Cells
            If c.MergeArea(1).Value >= lvl Then   'current level or greater?
                UnionRanges grp, c      'add this row to the current group
            Else
                CloseGroup grp          'group any collected rows
            End If
        Next c
        CloseGroup grp
    Next lvl
    
End Sub

'Handle closing out a group of collected cells and
'   reset `grp` to Nothing
Sub CloseGroup(ByRef grp As Range)
    If Not grp Is Nothing Then
        Debug.Print "Grouping " & grp.Address
        grp.EntireRow.Group
        Set grp = Nothing
    End If
End Sub

'Add range `rngToadd` to range `RngTot`
Sub UnionRanges(rngTot As Range, rngToAdd As Range)
    If rngTot Is Nothing Then
        Set rngTot = rngToAdd
    Else
        Set rngTot = Application.Union(rngTot, rngToAdd)
    End If
End Sub

Before and after:

You can automate the grouping of merged rows in Excel using VBA. Since your hierarchy is based on the left-most column, and your rows are merged in pairs, you need a script that:

Iterates through the sheet while considering merged rows.

Groups them based on hierarchy levels in the left-most column.

Ensures Excel’s Outline feature correctly applies grouping.

VBA Solution

This macro will loop through the sheet, detect hierarchical levels, and group rows accordingly while accounting for merged cells:

Sub AutoGroupMergedRows()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim startRow As Long
    Dim currentLevel As Integer, nextLevel As Integer

    ' Set active worksheet
    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' Find last used row

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ws.Outline.SummaryRow = xlAbove ' Ensure the grouping buttons appear above

    startRow = 2 ' Assuming headers are in row 1

    ' Loop through rows in steps of 2 (to account for merged rows)
    For i = startRow To lastRow Step 2
        currentLevel = ws.Cells(i, 1).Value
        If i + 2 <= lastRow Then
            nextLevel = ws.Cells(i + 2, 1).Value
        Else
            nextLevel = -1 ' Prevent out-of-range error
        End If

        ' Group if the next hierarchy level is greater
        If nextLevel > currentLevel Then
            ws.Rows(i & ":" & i + 1).Group
        End If
    Next i

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Grouping complete!", vbInformation
End Sub

How It Works:

1. Loops through the sheet in steps of 2 to handle merged rows properly.

2. Reads hierarchy values from the left-most column.

3. Groups rows when the next row’s level is greater.

4. Automatically applies grouping using Excel's Outline feature.

This should save you time when dealing with 5,000+ rows of data. Hope this helps! Let me know if you need modifications.