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

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

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