Open / Close Grouping through VBA

[Tim879]

Hi

Does anyone know if it's possible to expand / collapse groupings using
VBA. I tried using the macro recorder but no code is generated when
you expand / collapse a group.

Any suggestions?

Thanks
Tim

[JLGWhiz]

Not sure what you mean, but look at the Resize function.

[Tim879]

To clarify my first post....

I group a lot of my data (as opposed to hiding rows). To group the
data, I go to Data -> Group then you see the small + sign to left /
above the row / column you grouped.

I have some macros that do the grouping for me but I would like them
to automatically collapse the grouping for me after grouping the
selected rows / columns.

On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:

[Tim879]

I looked into resize but that doesn't work. I am not trying to resize
the range I have selected, I am just trying to collapse / expand the
grouping.

Thanks for the suggestion though. Any other ideas?

On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:

[Tim879]

Actually... was able to answer my own question...

If you set the row / column height to 0 or Hidden after grouping, the
group is collapsed. I'm not sure if this is just a work around. Does
anyone know a better way to do this?

Sub Group_Selection()

Dim myCol As Range
Dim myRow As Range
Dim R_or_C As String

On Error Resume Next

If Selection.Rows.Count = 65536 Then
R_or_C = "C"

ElseIf Selection.Columns.Count = 256 Then
R_or_C = "R"

Else
While R_or_C <> "R" And R_or_C <> "C"
R_or_C = UCase(InputBox("Do you want to Ungroup the selected
rows or columns. For Rows enter 'R' for columns enter 'C'."))

If R_or_C = "" Then Exit Sub
Wend
End If

Select Case R_or_C

Case "C"
For Each myCol In Selection.Columns
myCol.Group
myCol.Hidden = True
Next

Case "R"
For Each myRow In Selection.Rows
myRow.Group
myRow.Hidden = True
Next

End Select

End Sub

[JLGWhiz]

Nope, no more suggestions. I see now what you were attempting and all I know
about grouping is group or ungroup. Haven't really worked with it at all.

[Trevor Williams]

Hi Tim

Don't know if you still need this info but a quicker way of doing it
(without all your code) is:

ActiveSheet.Outline.ShowLevels RowLevels:=1 ' to collapse the rows
ActiveSheet.Outline.ShowLevels RowLevels:=2 ' to expand the rows

Regards

Trevor Williams

[mariodona]

As a novice in VBA coding, I find your code instructive and very useful. In fact, I'm using it on a spreadsheet with over 4,000 rows. However, doing this by hand is time cosuming. Can this be more automated?
My spreadsheet is of a "fully indented parts list" which shows the Bill of Materials for a large system. The first 5 columns (labeled LVL1, LVL2 ... LVL5) contain text indicating (obviously) the level of the item with respect to the top number. How, then, to have Excel select all rows tagged as LVL5 (i.e., '05' appears in column LVL5) and execute your code. Repeat this for rows tagged as LVL4 ('04' appears in column LVL4), etc., down to LVL1. I thank you in advance if you can help.

Tim879 wrote:

Actually... was able to answer my own question...

13-Mar-08

Sub Group_Selection()

On Error Resume Next

Select Case R_or_C

End Select

End Sub

Previous Posts In This Thread:

On Wednesday, March 12, 2008 11:04 AM
JLGWhi wrote:

RE: Open / Close Grouping through VBA

Not sure what you mean, but look at the Resize function.

"Tim879" wrote:

On Wednesday, March 12, 2008 12:10 PM
JLGWhi wrote:

Nope, no more suggestions.

Nope, no more suggestions. I see now what you were attempting and all I know
about grouping is group or ungroup. Haven't really worked with it at all.

"Tim879" wrote:

On Thursday, March 13, 2008 7:10 AM
Tim879 wrote:

Open / Close Grouping through VBA
Hi

Does anyone know if it's possible to expand / collapse groupings using
VBA. I tried using the macro recorder but no code is generated when
you expand / collapse a group.

Any suggestions?

Thanks
Tim

On Thursday, March 13, 2008 7:10 AM
Tim879 wrote:

To clarify my first post....
To clarify my first post....

I group a lot of my data (as opposed to hiding rows). To group the
data, I go to Data -> Group then you see the small + sign to left /
above the row / column you grouped.

I have some macros that do the grouping for me but I would like them
to automatically collapse the grouping for me after grouping the
selected rows / columns.

On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:

On Thursday, March 13, 2008 7:10 AM
Tim879 wrote:

I looked into resize but that doesn't work.
I looked into resize but that doesn't work. I am not trying to resize
the range I have selected, I am just trying to collapse / expand the
grouping.

Thanks for the suggestion though. Any other ideas?

On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:

On Thursday, March 13, 2008 7:10 AM
Tim879 wrote:

Sub Group_Selection()

On Error Resume Next

Select Case R_or_C

End Select

End Sub

On Tuesday, May 13, 2008 7:02 AM
TrevorWilliam wrote:

Hi TimDon't know if you still need this info but a quicker way of doing it
Hi Tim

Don't know if you still need this info but a quicker way of doing it
(without all your code) is:

ActiveSheet.Outline.ShowLevels RowLevels:=1 ' to collapse the rows
ActiveSheet.Outline.ShowLevels RowLevels:=2 ' to expand the rows

Regards

Trevor Williams

"Tim879" wrote:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Cross-Browser ASP.NET CSS-Only Menu UserControl
http://www.eggheadcafe.com/tutorials/aspnet/f32b452c-48ea-4ed8-96ce-d17d1b482676/build-a-crossbrowser-asp.aspx

[PY & Associates]

> Build a Cross-Browser ASP.NET CSS-Only Menu UserControlhttp://www.eggheadcafe.com/tutorials/aspnet/f32b452c-48ea-4ed8-96ce-d...

Not sure how the data file looks like.
Using Excel 2007 (not sure if 2003 version has this feature)
May be you can check this:

rows("25:30").group
rows("46:90").ungroup

[Habib Hussain]

(Sheetname).Outline.ShowLevels ColumnLevels:=2 or 1

(Sheetname).Outline.ShowLevels RowLevels:=2 or 1

The top one will collapse and expand the columns and the bottom one will collapse and expand the rows

> On Wednesday, March 12, 2008 11:04 AM JLGWhi wrote:

> Not sure what you mean, but look at the Resize function.
>
> "Tim879" wrote:

>> On Wednesday, March 12, 2008 12:10 PM JLGWhi wrote:

>> Nope, no more suggestions. I see now what you were attempting and all I know
>> about grouping is group or ungroup. Haven't really worked with it at all.
>>
>> "Tim879" wrote:

>>> On Thursday, March 13, 2008 7:10 AM Tim879 wrote:

>>> Hi
>>>
>>> Does anyone know if it's possible to expand / collapse groupings using
>>> VBA. I tried using the macro recorder but no code is generated when
>>> you expand / collapse a group.
>>>
>>> Any suggestions?
>>>
>>> Thanks
>>> Tim

>>>> On Thursday, March 13, 2008 7:10 AM Tim879 wrote:

>>>> To clarify my first post....
>>>>

>>>> I group a lot of my data (as opposed to hiding rows). To group the
>>>> data, I go to Data -> Group then you see the small + sign to left /
>>>> above the row / column you grouped.
>>>>
>>>> I have some macros that do the grouping for me but I would like them
>>>> to automatically collapse the grouping for me after grouping the
>>>> selected rows / columns.
>>>>
>>>>
>>>>
>>>> On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
>>>> wrote:

>>>>> On Thursday, March 13, 2008 7:10 AM Tim879 wrote:

>>>>> I looked into resize but that doesn't work. I am not trying to resize
>>>>> the range I have selected, I am just trying to collapse / expand the
>>>>> grouping.
>>>>>
>>>>> Thanks for the suggestion though. Any other ideas?
>>>>>
>>>>> On Mar 12, 11:04 am, JLGWhiz <JLGW...@discussions.microsoft.com>
>>>>> wrote:

>>>>>> On Thursday, March 13, 2008 7:10 AM Tim879 wrote:

>>>>>> Actually... was able to answer my own question...
>>>>>>

[Paul Croft]

Thanks for this code!

I had a similar need for it. However I had sub groups contained within the main groups in my spreadsheet and this code only expanded the main group.

If anyone else is in the same situation then this should work for you.

(Sheetname).Outline.ShowLevels ColumnLevels:=3

Complete noob guesswork to be honest but it worked a charm.