Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to find, Group, Ungroup rows
Hi, I have a worksheet with nested groups of rows, e.g., rows 1 to 10 are
grouped and within that group rows 5 to 10 are grouped. I'm trying to write code that will find the number of groups on the page and which rows they apply to so I can automate the insertion of new groups of rows between others. All I can find in Excel VBA Help is the basic syntax for the Group/Ungroup command. Can anyone suggest a resource for more advanced use/control of these? Any help is deeply appreciated, Don |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to find, Group, Ungroup rows
I don't know of a site where there is extensive documentation on it, but it
pretty much boils down to looping through the rows and checking the outlinelevel property. Here is code I posted a while ago that determines what the current outline level showing is: Public Function Level(Sh As Worksheet) Dim visRows As Range Dim rw As Range Dim maxLevel As Integer Set visRows = Sh.UsedRange.SpecialCells(xlVisible) Set visRows = Intersect(visRows, Sh.Columns(1)).EntireRow maxLevel = 0 For Each rw In visRows If rw.OutlineLevel <= maxLevel And maxLevel < 1 Then Exit For If rw.OutlineLevel maxLevel Then maxLevel = rw.OutlineLevel Next Level = maxLevel End Function This would only be appropriate if setting the outline only through the buttons. If it is being done manually, then there wouldn't be just one level. In any event, this should give you some ideas. -- Regards, Tom Ogilvy "donesquire" wrote: Hi, I have a worksheet with nested groups of rows, e.g., rows 1 to 10 are grouped and within that group rows 5 to 10 are grouped. I'm trying to write code that will find the number of groups on the page and which rows they apply to so I can automate the insertion of new groups of rows between others. All I can find in Excel VBA Help is the basic syntax for the Group/Ungroup command. Can anyone suggest a resource for more advanced use/control of these? Any help is deeply appreciated, Don |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use VBA to find, Group, Ungroup rows
Tom, I'll work through your example and try to apply it to my situation.
Thanks very much for your help and quick response, Don "Tom Ogilvy" wrote: I don't know of a site where there is extensive documentation on it, but it pretty much boils down to looping through the rows and checking the outlinelevel property. Here is code I posted a while ago that determines what the current outline level showing is: Public Function Level(Sh As Worksheet) Dim visRows As Range Dim rw As Range Dim maxLevel As Integer Set visRows = Sh.UsedRange.SpecialCells(xlVisible) Set visRows = Intersect(visRows, Sh.Columns(1)).EntireRow maxLevel = 0 For Each rw In visRows If rw.OutlineLevel <= maxLevel And maxLevel < 1 Then Exit For If rw.OutlineLevel maxLevel Then maxLevel = rw.OutlineLevel Next Level = maxLevel End Function This would only be appropriate if setting the outline only through the buttons. If it is being done manually, then there wouldn't be just one level. In any event, this should give you some ideas. -- Regards, Tom Ogilvy "donesquire" wrote: Hi, I have a worksheet with nested groups of rows, e.g., rows 1 to 10 are grouped and within that group rows 5 to 10 are grouped. I'm trying to write code that will find the number of groups on the page and which rows they apply to so I can automate the insertion of new groups of rows between others. All I can find in Excel VBA Help is the basic syntax for the Group/Ungroup command. Can anyone suggest a resource for more advanced use/control of these? Any help is deeply appreciated, Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Group and Ungroup | Excel Discussion (Misc queries) | |||
group&ungroup | Charts and Charting in Excel | |||
On protected Excel sheet users be able to group/ungroup rows/colum | Excel Worksheet Functions | |||
Help with Group/Ungroup | Excel Discussion (Misc queries) | |||
how do you group and ungroup rows or columns in new Excel? | Excel Worksheet Functions |