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

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

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

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

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

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

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