' 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 SubAs 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...
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.