Group rows based on indent level

[fprzekop]

I need help with some VBA to loop through a spreadsheet and Group rows with an indent level > 0. Indent level 0 is the highest level summary for the data, detail is broken out at indent level 1, 2 etc.. A data sample follows:

Cash & Short Duration
Cash and Money Market
Money Market 1
Money Market Fund 2
Fixed Income
Corporate Bonds
Corp Bond Holding 1
Corp Bond Holding 2
High-Yield Bonds - Taxable
HY Fund 1
HY Fund 2

In this example I need to Group "Money Market 1" + "Money Market 2", and then also group "Cash and Money Market" + "Money Market 1" + "Money Market 2" at the next level. This would allow for two levels of outline functionality to roll up data. My data set does not have formula subtotals so the Auto Outline feature doesn't work here. The data is however indented at each level. Many thanks in advance for any tips to solve the coding challenge.

 

[fprzekop]

Apologies for the poor formatting I haven't solved how to post code and retain the format. The code below loops through my data set and groups any consecutive rows with IndentLevel=2

Sub GroupOnIndent()


Dim rowMax As Integer
Dim rowCount As Integer
Dim r As Integer

Range("A1").Select
Selection.End(xlDown).Select
rowMax = ActiveCell.Row

rowCount = 0

' data starts on row 3. This code groups consectuive items indented with IndentLevel = 2

For r = 3 To rowMax

Range("A" & r).Select

If ActiveCell.IndentLevel = 2 And IsEmpty(ActiveCell.Value) = False Then

rowCount = rowCount + 1

Else

If rowCount > 0 Then

ActiveCell.Offset(-rowCount).Resize(rowCount).Select

Selection.Rows.Group

rowCount = 0

End If

End If

Next r

End Sub