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