I’m working on macro that will expand / collapse groups. The idea is to have label buttons ‘show detail’ and ‘hide detail’ that sit above the group
You can see an example here
http://www.youtube.com/watch?v=70efsNWwuIU#t=14m00s
here’s the macro so far

Sub group_expand()
''Macro to show the details of a group

‘Find the row that the button that called the procedure is in

buttonRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell .Row

'Check to see if grouping details are already showing
If Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True Then
‘If grouping detail is already showing, then do nothing
Else
‘If the grouping detail is hidden, then show the detail
Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True
End If
End Sub

There are a few improvements I would like to make.

1.Find the top row of the next grouping. Currently the macro is hardcoded and label has to be exactly 1 row above the top of the grouping. I can find the row location of the button, however I don’t know how to find the top row of the next group.

In other words, I’d replace the (buttonRow + 1) with (buttonRow + distance to top row of next grouping),

e.g.

If Worksheets("IS").Rows(buttonRow + 1).ShowDetail = True

To

If Worksheets("IS").Rows(buttonRow + [distance to top row of next grouping]).ShowDetail = True

2. Find the height of the grouping

Here’s where I’m up to.

ActiveSheet.Activate
Set myRange = ActiveCell.CurrentRegion
lastRow = myRange.Rows.Count

This will return the correct height of the grouping. However, I need this driven off a cell reference, not ActiveCell.


My first thought was to make the following change:

Set myRange = Cells(BR + [distance to top row of next grouping],1).CurrentRegion
lastRow = myRange.Rows.Count

But this doesn’t work. The value of lastRow is always 1 in this example.

If anyone has a better way to determine the height of a grouping I would love to hear.

3. Finally, check to see if the summary rows are at the top of the grouping or at the bottom of the grouping (i.e. does the +/- show up at the top of the grouping or at the bottom of the grouping).

As this is currently coded, the macro only works if the summary rows are set to be at the top of the grouping. If the summary row (+/- button) is at the bottom of the grouping then I need to add not 1, but the 1 + height of the grouping to the buttonRow.

I’m sure the location of the summary row/(+/-) is a property of groupings, I just don’t know what the property is.