Add dynamic subtotals and then hide all blank rows

[agnesz]

The attached report is a dynamic business recap which will have sections either added or removed on a weekly basis.

Once the columns are added/deleted I'd like the recap to subtotal at each change in "region name" column A.

The subtotals should be as follows:
Sum for TY, LY, PL
and % changes in TY Pen, % LY, % Pln (these 3 are not critical to have in there)

After the subtotal processes and creates a grand total row, I'd like any rows that are totally blank to be hidden.

Thanks as always, you guys rock.
:dunno

[Bob Phillips]

Sub InsertSubtotals()
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A6:BB" & LastRow).Subtotal _
GroupBy:=1, _
Function:=xlSum, _
TotalList:=Array(16, 17, 18), _
Replace:=True, _
pageBreaks:=False, _
SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2
End With
End Sub


ALso moved it tgo main Excel forum

[agnesz]

This only subtotals the first set of columns (see attachment - it has two sets of columns) but I need it to subtotal any additional sections of data, these will vary every week. Is there any way to make the subtotaling dynamic?