Open / Close Grouping through VBA

Tim879 · Mar 12, 2008

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 · Mar 12, 2008

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

Tim879 · Mar 12, 2008

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.

Tim879 · Mar 12, 2008

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?

Tim879 · Mar 12, 2008

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 · Mar 12, 2008

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 · May 13, 2008

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

Luth · Feb 7, 2009

Opening and closing an excel outline in VBA

The thread is a bit old, but for posterity's sake, this is my solution to this problem...

Code:

' Expand the grouping one level for the currently selected text
Sub ExpandOutline()

	Application.ScreenUpdating = False

	Dim level As Integer
	Dim thisLevel As Integer
	Dim startingCell As Range
	
	Set startingCell = ActiveCell
	
	level = ActiveCell.Rows(1).OutlineLevel + 1
	
	Do
		ActiveCell.Offset(1, 0).Select
		thisLevel = ActiveCell.Rows(1).OutlineLevel
		If thisLevel = level Then
			ActiveCell.Rows(1).Hidden = False
		End If
	Loop While thisLevel >= level
	
	startingCell.Offset(1, 0).Select
		
End Sub

' Collapse the outline level of the currently selected text
Sub CollapseOutline()

	Application.ScreenUpdating = False

	Dim level As Integer
	Dim thisLevel As Integer
	Dim startingCell As Range
	
	Set startingCell = ActiveCell
	
	level = ActiveCell.Rows(1).OutlineLevel
	If level = 1 Then
		End
	End If
	
	Do
		ActiveCell.Offset(1, 0).Select
		thisLevel = ActiveCell.Rows(1).OutlineLevel
		If thisLevel = level Then
			ActiveCell.Rows(1).Hidden = True
		End If
	Loop While thisLevel >= level
	
	startingCell.Offset(1, 0).Select
	Do
		ActiveCell.Offset(-1, 0).Select
		thisLevel = ActiveCell.Rows(1).OutlineLevel
		If thisLevel = level Then
			ActiveCell.Rows(1).Hidden = True
		End If
	Loop While thisLevel >= level
		
End Sub

Mario Dona · Apr 16, 2010

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

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

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

Sub Group_Selection(

Dim myCol As Rang
Dim myRow As Rang
Dim R_or_C As Strin

On Error Resume Nex

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

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

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

If R_or_C = "" Then Exit Su
Wen
End I

Select Case R_or_

Case "C
For Each myCol In Selection.Column
myCol.Grou
myCol.Hidden = Tru
Nex

Case "R
For Each myRow In Selection.Row
myRow.Grou
myRow.Hidden = Tru
Nex

End Selec

End Su

Previous Posts In This Thread:

RE: Open / Close Grouping through VBA
Not sure what you mean, but look at the Resize function

:

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

:

Open / Close Grouping through VBA
H

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

Any suggestions

Thank
Tim

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

I group a lot of my data (as opposed to hiding rows). To group th
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 the
to automatically collapse the grouping for me after grouping th
selected rows / columns

On Mar 12, 11:04 am, JLGWhiz <[email protected]
wrote:

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

Thanks for the suggestion though. Any other ideas

On Mar 12, 11:04 am, JLGWhiz <[email protected]
wrote:

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

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

Sub Group_Selection(

Dim myCol As Rang
Dim myRow As Rang
Dim R_or_C As Strin

On Error Resume Nex

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

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

Els
While R_or_C <> "R" And R_or_C <> "C
R_or_C = UCase(InputBox("Do you want to Ungroup the selecte
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

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

:

Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Cross-Browser ASP.NET CSS-Only Menu UserControl
http://www.eggheadcafe.com/tutorial...ce-d17d1b482676/build-a-crossbrowser-asp.aspx

PY & Associates · Apr 16, 2010

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, doingthis 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. Ithank you in advance if you can help.

Tim879 wrote:

Actually... was able to answer my own question...
13-Mar-08

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

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.

:

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.

:

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 <[email protected]>
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 <[email protected]>
wrote:

On Thursday, March 13, 2008 7:10 AM

Tim879 wrote:

Actually... was able to answer my own question...
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

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

:

Submitted via EggHeadCafe - Software Developer Portal of Choice
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

chenos7 · May 18, 2010

I needed a solution to collapse the column's group and I did it using one of the solutions above with a small change:

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

Thank you,
Noam

Habib Hussain · Apr 21, 2011

(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

Paul Croft · Apr 21, 2011

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.

Tom the Toolman · Sep 26, 2017

I know this is an old thread, but information lives forever, right?

If I just want to expand or collapse one particular group, not all the groups on the sheet, I use ShowDetail. Say rows 160 to 170 are a grouped region.

rows(167).ShowDetail = true ' expand the group
rows(162).ShowDetail = false ' collape the group

So you can pick any row inside the group. This works for nested groups too.

Tom

Open / Close Grouping through VBA

Tim879

JLGWhiz

Tim879

Tim879

Tim879

JLGWhiz

Trevor Williams

Luth

Mario Dona

PY & Associates

chenos7

Habib Hussain

Paul Croft

Tom the Toolman