Group rows based on indent level

fprzekop

Board Regular
Joined
Jun 25, 2002
Messages
74
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

fprzekop

Board Regular
Joined
Jun 25, 2002
Messages
74
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
 

Forum statistics

Threads
1,136,257
Messages
5,674,660
Members
419,521
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top