Excel grouped data
 

I am using an Excel 2002 spreadsheet with 20+ worksheets that all contain
grouped data. Can anyone help with a macro that will automatically expand the
grouped data in each of the worksheets, and go to the last cell.

Similarly a macro is required to collapse the grouped data in each of the
worksheets, and go to the home cell.

Excel grouped data
 

If your data has 4 levels of grouped rows then you can use;

Sub Collapse()
ActiveSheet.Outline.ShowLevels RowLevels:=4
ActiveSheet.Outline.ShowLevels RowLevels:=3
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub
Sub Expand()
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=3
ActiveSheet.Outline.ShowLevels RowLevels:=4
End Sub

"Grey Old Man" wrote:

I am using an Excel 2002 spreadsheet with 20+ worksheets that all contain
grouped data. Can anyone help with a macro that will automatically expand the
grouped data in each of the worksheets, and go to the last cell.

Similarly a macro is required to collapse the grouped data in each of the
worksheets, and go to the home cell.

Excel grouped data
 

Thanks, this would be OK for a single worksheet, but what I am really looking
for is a macro to expand a variable number of groups (both rows and columns)
for a variable number of worksheets. A similar macro is also required to
collapse the groups after they have been used.

"om" wrote:

If your data has 4 levels of grouped rows then you can use;

Sub Collapse()
ActiveSheet.Outline.ShowLevels RowLevels:=4
ActiveSheet.Outline.ShowLevels RowLevels:=3
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub
Sub Expand()
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=3
ActiveSheet.Outline.ShowLevels RowLevels:=4
End Sub

"Grey Old Man" wrote:

I am using an Excel 2002 spreadsheet with 20+ worksheets that all contain
grouped data. Can anyone help with a macro that will automatically expand the
grouped data in each of the worksheets, and go to the last cell.

Similarly a macro is required to collapse the grouped data in each of the
worksheets, and go to the home cell.