Grouping via VBA (dynamic record set)

[TheWennerWoman]

Hello,

Hope someone can help.

I have a refreshable sheet named "Report" with data running from A6 down to A150. Months from April 2019 to March 2020 run across the page.

This data in column A is in the format
AREA1
Worker 1
Worker 2
AREA2
Worker 1
Worker 2
Worker 3

etc and it all finishes with a Grand Total.

I need to group it all by Area so that the default view is at AREA level; the end users can then expand the sections they need.

However, in the above example, next month AREA1 might have a Worker 3 added; so I can't have static groupings.

I need something smart that, when the workbook opens clears the groupings and then reapplies them based on what's in column A.....so when it encounters text containing AREA, it knows that's a new grouping.

Does any of that make sense?

Thanks in advance as always.

Oh, PS: I can't use a pivot table for this!

 

[Fluff]

How about

Private Sub Workbook_Open()
    Dim Rng As Range
    With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
        With Range("A6", Range("A" & Rows.Count).End(xlUp).Offset(-1))
            .ClearOutline
            .Replace "area", "=xxxarea", xlPart, , False, , False, False
            For Each Rng In .SpecialCells(xlConstants).Areas
                Rng.EntireRow.Group
            Next Rng
            .Replace "=xxxarea", "AREA", xlPart, , False, , False, False
        End With
    End With
End Sub

This needs to go in the ThisWorbook module, change sheet name to suit

 

[TheWennerWoman]

Thanks Fluff but it falls over with "no cells were found".

Debug stops on "For Each Rng In .SpecialCells(xlConstants).Areas"

 

[Fluff]

In that case, can you please show what your data actually looks like.