|
| 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
November 15th, 2009, 04:31 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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
November 16th, 2009, 05:33 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
November 17th, 2009, 05:23 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 | |
|
|
Linear Mode
