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

Open / Close Grouping through VBA
 

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

"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

Open / Close Grouping through VBA
 

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
wrote:
Not sure what you mean, but look at the Resize function.

"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

Open / Close Grouping through VBA
 

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
wrote:
Not sure what you mean, but look at the Resize function.

"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

Open / Close Grouping through VBA
 

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 Mar 12, 11:13 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
wrote:

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

"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

Open / Close Grouping through VBA
 

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:

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
wrote:
Not sure what you mean, but look at the Resize function.

"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

Open / Close Grouping through VBA
 

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:

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

I like your code, but...
 

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, 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 Mar 12, 11:13 am, Tim879 wrote:

Previous Posts In This Thread:

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

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
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
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 Mar 12, 11:13 am, Tim879 wrote:

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...owser-asp.aspx

I like your code, but...
 

On Apr 16, 8:32*am, Mario Dona wrote:
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, 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 Mar 12, 11:13 am, Tim879 wrote:

Previous Posts In This Thread:

On Wednesday, March 12, 2008 11:04 AM

JLGWhi wrote:

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
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
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 Mar 12, 11:13 am, Tim879 wrote:

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 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

I like your code, but...
 

(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
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
wrote:


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

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 Mar 12, 11:13 am, Tim879 wrote:


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

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:


On Thursday, April 15, 2010 8:32 PM Mario Dona wrote:

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.

I like your code, but...
 

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.