If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|