VBA to expand an Outline ( Grouping ) . ( Open Grouped Rows )

[DocAElstein]

VBA to expand an Outline ( Grouping ) . ( Open grouped Rows )


Hi
Does anyone know if it is possible to expand an Outline (grouping) using VBA. By googling I have seen the question asked, but not yet found it to be answered. ( Sorry if I use slightly inappropriate terms, as I am translating from my German Excel ! )

In specific when I click on a cell in column G, I want the 49 rows before the row in which the clicked cell is to be “opened.” ( In the jargon I think the “Outline Object in Left hand margin is required to be brought to show Level 2” )

I tried using the macro recorder but no code is generated when you expand ( or collapse ) any group.

_..............................

Some more detail to the exact requirement:

My Worksheet is currently a bit under 18000 rows. Food Types are grouped in groups of 50. ( Or more specifically 49 grouped rows followed by an un grouped Header Row ). In column G every 50th Cell contains a Heading of a Type of Food ( Ketchup, Biscuit , Cakes, Protein Bars etc. etc.. ) So effectively every 50th row is a heading row. ( Just to confuse everything I start at 22. So heading rows are 71, 121, 171 ... etc. ; Correspondingly rows 22-70, 72-120, 122-170 are outlined ( grouped ) together )

So The 49 outlined ( grouped ) rows before each heading row “belong” to that heading. I am only using one level of Outline ( grouping. ). I think this means i want my code to get at just a small part of Level 2 ? ! ( “or display in the left hand margin a small part of the Level 2 Outline Object” ) )

Just to make clear again. This following code comes somewhere in the ball park of what I want. With this code, when I click on any Heading Cell in Column G, it opens all groups in the Worksheet. ( “It shows all of level 2 Outline Object, in the Left hand margin” )

2 Codes here, with and without explaining 'comments
Short Code:

[color=darkgreen]'' Short Simplified code:[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_SelectionChange([color=blue]ByVal[/color] Target [color=blue]As[/color] Range)
Rem 1)
[color=blue]Dim[/color] G_Head [color=blue]As[/color] Range
[color=blue]Set[/color] G_Head = Range("G22:G17201")
Rem 2)
    [color=blue]If[/color] ((Target.Row - 21) / 50) - (Int((Target.Row - 21) / 50)) = 0 [color=blue]Then[/color]
        [color=blue]If[/color] [color=blue]Not[/color] Application.Intersect(Target, G_Head) [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color]
        Outline.ShowLevels RowLevels:=2
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]

Full Code:

[color=darkgreen]'THIS CODE MUST GO IN SHEET CODE MODULE[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_SelectionChange([color=blue]ByVal[/color] Target [color=blue]As[/color] Range) [color=darkgreen]'Events code: Kicks in when any selection change is made ( Or When you click anywhere in the Sheet to which this Sheet Code Module belongs )[/color]
Rem 1) [color=darkgreen]'Worksheets Info[/color]
[color=blue]Dim[/color] G_Head [color=blue]As[/color] Range [color=darkgreen]'Declare G_Head as "Pointer" ("place to go to where there is an appropriate sized "Pigeion Hole" to hold the ""code lines" written on "piece of paper"" to hold all infomation required for a Range Object...... Or if you like a piece of paper with all instructions on it as to what to do when the code hits "G_Head". Initially the paper is a sort of Blue Print, not yet filled in with specific values[/color]
[color=blue]Dim[/color] wksHere [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksHere = ThisWorkbook.Worksheets("Sheet1") [color=darkgreen]' "Blue Print"Pointer"" appropriate for a Worksheet made, then filed in for a specific Worksheet. Mostly redundant as code is in This sheet Module usually. - Therefore,  All unqualified range calls will go to the Sheet in which this code is in[/color]
[color=blue]Set[/color] G_Head = wksHere.Range("G22:G17201") [color=darkgreen]'Specific Instructions on "Blue Print Paper" for current info for this specific ( General Food Type Heading ) Range. Initial memory locations assigned for start at least of any given values. May change Later. But the "Pointer Paper" now has enougth info to allow all usual things to be done or got from the Range Object[/color]
[color=darkgreen]'[/color]
Rem 2) 'Checks to only do anything for a Heading Selection
[color=darkgreen]'2a) We are only interested in the heading Row which is at constant intervals[/color]
[color=blue]Dim[/color] RowTP [color=blue]As[/color] [color=blue]Long[/color], Frac [color=blue]As[/color] [color=blue]Double[/color] [color=darkgreen]'Declared Variables for row of where I click (Top of if rows selected), and a check to see if i am at a heading row. These "Pointer" here created will typically have all info as the "physical size" of these variable types is independant of the value, for example, 001.00 has same "Phsical size" as 100.01 etc. So the Pointer will contain for example a fixed memory location where the value of any set variable will be found.   (  ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )[/color]
[color=blue]Let[/color] RowTP = Target.Row [color=darkgreen]''**Target is used by VBA in this sort of code as a reserved named Range for the last selection. (effectively where I **targeted with my finger!)  .The .Row Property returns Top or start row of this range in the Worksheet.[/color]
[color=blue]Let[/color] Frac = ((RowTP - 21) / 50) - (Int((RowTP - 21) / 50)) [color=darkgreen]'Will return excactly zero for a row of 71, 121, 171 ... etc..[/color]
    [color=blue]If[/color] Frac = 0 [color=blue]Then[/color] [color=darkgreen]'This is the case that a heading row is selected, in which case we look further....otherwise.....[/color]
    [color=darkgreen]'2b) Check if we got a G_Head Intersecstion. ( Check if we clicked in our Column G Range, and at the same time determine that cell ( or Top Left if we did a Multi cell selection )... then if so....[/color]
    [color=blue]Dim[/color] PenatrationAttempt [color=blue]As[/color] Range [color=darkgreen]'For the returned "thing" from the VBA This Variable is declared such as to hold one of two similar Results. Consider the following. When I click on a cell VBA effectivelly shoots( from my "**Target" ) out the strands of a brush "held in one hand". Our G_Head Range is "held in the other hand" offset by an angle but such as to possibly catch a Penetration from that held in the other hand. Should the "Penetration attempt" cause a "connection" then this represents a specific Range object. In other words a "sucess" here will be if Range Target and G_Head cross over.  The Retuned "thing" from the .Intersect Method will be a Range Object of that clicked cell. If no succesful "Penetration" , or "missed shot" then the .Intersect Method will still "attempt" to return a Declared Range Object. But it "shoots a blank" - The "Blue Print" has not been filled in. This situation is, as in the case of any non assigned but declared Range Object as "Nothing"[/color]
    [color=blue]Set[/color] PenatrationAttempt = Application.Intersect(Target, G_Head) [color=darkgreen]''..... As noted above we are looking for a penetration of VBA "shot out lines" representation internally of Target with the G_Head. In other words a cross over or interception of these two "Ranges", our defined specific G_head Range and the pseudo range Object Interpretation of "Target"                                                                                                                                                                                                                                                                                                                                                                                                                                                           ----- How VBA "Works" ---http://www.mrexcel.com/forum/general-excel-discussion-other-questions/929381-visual-basic-applications-split-function-third-argument-refers-maximum-outputs-%93when-splitting-stops-%94.html#post4468[/color]
        [color=blue]If[/color] [color=blue]Not[/color] PenatrationAttempt [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color] [color=darkgreen]'Case of not getting a Nothing. So I penetrated the Brush strands to get a sucessful return ( Range Object )[/color]
        [color=darkgreen]'2a) Got a penetration so open up all outlined ( grouped ) rows.[/color]
        wksHere.Outline.ShowLevels RowLevels:=2 [color=darkgreen]'Using here the Outline ShowLevels Property to return an Outline Object (what you see in the Left Hand Margin) of all open first level Outline ( group )[/color]
        [color=blue]Else[/color] [color=darkgreen]'Case not clicked in our G Column Range. Do Nothoing. redundant Code[/color]
        [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'End intersection check[/color]
    [color=blue]Else[/color] [color=darkgreen]'....otherwise row selected was not a heading row. Redundant code as no action taken in this case[/color]
    [color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]

_...................................................

But I only want it to open the 49 rows before the row of the clicked cell. So I want this simplified “Pseudo code” to work.....

[color=darkgreen]'' Pseudo Code of Form I want : UnGroup A Single Food Type Only[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_SelectionChange([color=blue]ByVal[/color] Target [color=blue]As[/color] Range)
[color=blue]Dim[/color] G_Head [color=blue]As[/color] Range
[color=blue]Set[/color] G_Head = Range("G22:G17201")
    [color=blue]If[/color] (((Target.Row - 21) / 50) - (Int((Target.Row - 21) / 50)) = 0) And ([color=blue]Not[/color] Intersect(Target, G_Head) [color=blue]Is[/color] [color=blue]Nothing[/color]) [color=blue]Then[/color]
    Target.Offset(-49, 0).Resize(49).Outline.ShowLevels RowLevels:=2 [color=darkgreen]' Go back up 50 rows to the start of the Outlined ( Grouped ) Range, resize to the size of the Outlined ( Grouped Range ) and open that range to the level 2.  THIS IS A “PSEUDO” CODE LINE AND IT ERRORS!!!![/color]
    Else: [color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]

_...............................
Here a screen shot of a small part of what i have ( The “Before” ): (Note you do not see in this screen shot, but in the left hand margin small boxes are to be seen for this Level 1 Outline Object ( all my outline ( groups) are closed )

Using Excel 2007

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
19​		​	​	​	​	​	​	​
20​		​	​	​	​	​	​	300​
21​		​	​	​	​	​	​	​
71​		​	​	​	​	​	SenfKetchup​	​
121​		​	​	​	​	5​	AufstrichFettNut/FettSussichkeitMM/​	​
171​		​	​	​	​	​	"TrannsKeksWaffel&Co​	​

Sheet1

And here ( The “After” )what I would like to get when clicking for example on G71. (Note you do not see but in the left hand margin the small box in the left hand margin alongside row 71 is gone and the Outline Object is displayed as open.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
19​		​	​	​	​	​	​	​
20​		​	​	​	​	​	​	300​
21​		​	​	​	​	​	​	​
22​		​	​	​	​	​	​	​
23​		​	​	​	​	​	​	​
24​		​	​	​	​	​	​	​
25​		​	​	​	​	​	​	​
26​		​	​	​	​	​	​	​
27​		​	​	​	​	​	​	​
28​		​	​	​	​	​	​	​
29​		​	​	​	​	​	​	​
30​	TomatenKetchupLight	​	​	​	​	​	​	50​
31​	PicalliliLight	​	​	​	​	​	​	54​
32​	Picalilli	​	​	​	​	​	​	67​
33​		​	​	​	​	​	​	​
34​	SainsburysBrownSauceReduzieret	​	​	​	​	​	​	84​
35​		​	​	​	​	​	​	​
36​	SENF	​	​	​	​	​	​	86​
37​	balsamico 87/0/1/16/13/0/,05/	​	​	​	​	​	​	87​
38​	_BRN SAUCE_BrounSource	​	​	​	​	​	​	95​
39​		​	​	​	​	​	​	​
40​	barbecueketchup	​	​	​	​	​	​	109​
41​	barbecueSauce	​	​	​	​	​	​	172​
42​	Grill&Steak	​	​	​	​	​	​	118​
43​	Sweetpickle	​	​	​	​	​	​	130​
44​	EnglishSenf	​	​	​	​	​	​	170​
45​	MeerrettichJogSauce	​	​	​	​	​	​	300​
46​		​	​	​	​	​	​	​
47​	WaldenfarmsChocolatesyrup	​	​	​	​	​	​	10​
48​		​	​	​	​	​	​	​
49​		​	​	​	​	​	​	​
50​		​	​	​	​	​	​	​
51​		​	​	​	​	​	​	​
52​		​	​	​	​	​	​	​
53​		​	​	​	​	​	​	​
54​	SteakGrillSalz	​	​	​	​	​	​	84​
55​	KrauterPikantSalz	​	​	​	​	​	​	87​
56​	MeerSalzUndTraubenZucker1to1	​	​	​	​	​	​	111​
57​		​	​	​	​	​	​	​
58​		​	​	​	​	​	​	​
59​		​	​	​	​	​	​	​
60​		​	​	​	​	​	​	​
61​		​	​	​	​	​	​	​
62​	StadaElotrans	​	​	​	​	​	​	288​
63​	KochsalzersatzNatriumarmKaliumreich	​	​	​	​	​	​	​
64​	BlumenkohlCremesuppe(Pulver),	​	​	​	​	​	​	450​
65​	RoestZweibel	​	​	​	​	​	​	580​
66​	tellofixReinPflanzlich	​	​	​	​	​	​	200​
67​	KClassicKlaregemuesebruehe	​	​	​	​	​	​	200​
68​	BioSonneBioBouillionRind	​	​	​	​	​	​	178​
69​	BioSonneBioBouillionHuhn	​	​	​	​	​	​	196​
70​	BioSonneBioBouillionGemuese	​	​	​	​	​	​	201​
71​		​	​	​	​	​	SenfKetchup​	​
121​		​	​	​	​	5​	AufstrichFettNut/FettSussichkeitMM/​	​
171​		​	​	​	​	​	"TrannsKeksWaffel&Co​	​

_.............

The following screen shots may help explain a bit better here.
The “Before”



The “After”



_...............................................

I have tried very hard on this. I do have a workaround. But it is very messy :

_1) In place of the above codes, a similar Events Code is used to both UnGroup and unHide the Selected Food Type. ( 'THIS CODE MUST GO IN SHEET CODE MODULE )

[color=darkgreen]'Workaround Part 1) Ungroup and Unhide ( if not already! ) for Selected Food Type[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_SelectionChange([color=blue]ByVal[/color] Target [color=blue]As[/color] Range)
[color=blue]Dim[/color] G_Head [color=blue]As[/color] Range, RowTP [color=blue]As[/color] [color=blue]Long[/color], Frac [color=blue]As[/color] [color=blue]Double[/color]
[color=blue]Set[/color] G_Head = Range("G22:G17201")
[color=blue]Let[/color] RowTP = Target.Row
[color=blue]Let[/color] Frac = ((RowTP - 21) / 50) - (Int((RowTP - 21) / 50))
    [color=blue]If[/color] Frac = 0 [color=blue]Then[/color]
        [color=blue]If[/color] [color=blue]Not[/color] Intersect(Target, G_Head) [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color]
            [color=blue]With[/color] Rows("" & (RowTP - 49) & ":" & (RowTP - 1) & "")
                [color=blue]If[/color] .OutlineLevel > 1 [color=blue]Then[/color] [color=darkgreen]'This prevents error if already no grouping ![/color]
                .Ungroup
                .EntireRow.Hidden = [color=blue]False[/color]
                Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]' ! case already no Grouping[/color]
            [color=blue]End[/color] [color=blue]With[/color]
        Else: [color=blue]End[/color] [color=blue]If[/color]
    Else: [color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]

_..........................................

_2 A second code ( CODE MUST GO IN THISWORKBOOK CODE MODULE ) re Groups ( and Hides ( closes ) ) on closing any Ungrouped and unhidden Food Type Section....

[color=darkgreen]'Workaround Part 2) ReGroup on closing. CODE MUST GO IN THISWORKBOOK CODE MODULE[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Workbook_BeforeClose(Cancel [color=blue]As[/color] [color=blue]Boolean[/color])
                [color=darkgreen]'Sub test() 'To test code[/color]
Rem 1) [color=darkgreen]'Worksheets Info[/color]
[color=blue]Dim[/color] wksHere [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wksHere = ThisWorkbook.Worksheets("Sheet1") [color=darkgreen]'[/color]
Rem 2) 'Outline (Grouping) ranges info.   'Assumes all but Heading row is grouped and that the Group is all the rows before the heading up to last Heading.
[color=blue]Dim[/color] rStt [color=blue]As[/color] [color=blue]Long[/color], rStp [color=blue]As[/color] [color=blue]Long[/color], Sprg [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Start, Stop heading, Step between.[/color]
[color=blue]Let[/color] rStt = 71: [color=blue]Let[/color] rStp = 17221: [color=blue]Let[/color] Sprg = 50
Rem 3) [color=blue]Do[/color] all groupings, if not already grouped
[color=blue]Dim[/color] Cnt [color=blue]As[/color] [color=blue]Long[/color]
    [color=blue]For[/color] Cnt = rStt [color=blue]To[/color] rStp [color=blue]Step[/color] Sprg
        [color=blue]If[/color] wksHere.Rows("" & Cnt - 49 & ":" & Cnt - 1 & "").OutlineLevel = 1 [color=blue]Then[/color]
        wksHere.Rows("" & Cnt - 49 & ":" & Cnt - 1 & "").Group
        [color=blue]Else[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    [color=blue]Next[/color] Cnt
Rem 4) Option to "close" groups
wksHere.Outline.ShowLevels RowLevels:=1
Rem 5) Save the changes done by this code
ThisWorkbook.Save
[color=blue]End[/color] [color=blue]Sub[/color]

_...................................................

! The workaround will do, but I wanted to check if anyone can give a simpler alternative.

Thanks
Alan

 

[DocAElstein]

Hi,
The following is still a workaround. But it is simpler and suits very well the particular problem here:

The idea is based on part 1) of the last Workaround. But now, before you Ungroup and Unhide to “pseudo” open the Level ( grouping ), you first check to see if you last Ungrouped and Unhid a Food Type section. If so you then regroup it ( Add a Second Level Object to that section ). This is facilitated by giving the required range Information of the last Ungrouped and Unhid Food Type section to a Globlal variable. This variable is then checked and if found to be full the range is regrouped.
( For simplicity sake I use a Long for the row number as my Global variable.

Here is my workaround specific for my requirement

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Dim[/color] myLongGloby [color=blue]As[/color] Long [color=darkgreen]'     http://www.mrexcel.com/forum/excel-questions/913284-creating-global-variables.html#post4391743[/color]
[color=blue]Private[/color] [color=blue]Sub[/color] Worksheet_SelectionChange([color=blue]ByVal[/color] Target [color=blue]As[/color] Range)
Rem 1) Check for last entry of Selected Food Heading Range
    [color=blue]If[/color] myLongGloby > 0 [color=blue]Then[/color]
        [color=blue]If[/color] Rows("" & myLongGloby - 49 & ":" & myLongGloby - 1 & "").OutlineLevel = 1 [color=blue]Then[/color] [color=darkgreen]'Level 1 is no Grouping[/color]
        Rows("" & myLongGloby - 49 & ":" & myLongGloby - 1 & "").Group
        [color=blue]Let[/color] myLongGloby = 0
        [color=blue]Else[/color]
        [color=blue]End[/color] [color=blue]If[/color]
    Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'No "re-Group" action if no Last heading Selection[/color]
    Rem 2) Original [color=darkgreen]'Workaround Part 1)from Post #1 .... Ungroup and Unhide to "pseudo" Open the Level ( Grouping ),[/color]
    [color=blue]Dim[/color] G_Head [color=blue]As[/color] Range: [color=blue]Set[/color] G_Head = Range("G22:G17201")
        [color=blue]If[/color] [color=blue]Not[/color] Intersect(Target, G_Head) [color=blue]Is[/color] [color=blue]Nothing[/color] [color=blue]Then[/color] [color=darkgreen]'case Penetration of selection in G Column, so..[/color]
        [color=darkgreen]'2a) "Pseudo" Open the Level ( Grouping ), (If Heading cell was selected)[/color]
        [color=blue]Dim[/color] RowTP [color=blue]As[/color] Long: [color=blue]Let[/color] RowTP = Target.Row [color=darkgreen]'This will return Top Row of selection. ( In our case this will be the typically the selected heading Cell )[/color]
        [color=blue]Dim[/color] Frac [color=blue]As[/color] Double: [color=blue]Let[/color] Frac = ((RowTP - 21) / 50) - (Int((RowTP - 21) / 50))
            [color=blue]If[/color] Frac = 0 [color=blue]Then[/color] [color=darkgreen]'Frac will be zero for rows selection 71, 121, 171  ... etc which are the heading rows[/color]
                [color=blue]With[/color] Rows("" & (RowTP - 49) & ":" & (RowTP - 1) & "")
                    [color=blue]If[/color] .OutlineLevel > 1 [color=blue]Then[/color] [color=darkgreen]'This prevents error if already no grouping ![/color]
                    .Ungroup [color=darkgreen]'Would error if no grouping[/color]
                    .EntireRow.Hidden = [color=blue]False[/color] [color=darkgreen]'Will not error if already Un Hidden[/color]
                    [color=darkgreen]'2b 1b) Set Infomation for last selected Heading[/color]
                    myLongGloby = RowTP [color=darkgreen]'Set Global variable to indicate last heading Selection.[/color]
                    Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]' ! case already no Grouping[/color]
                [color=blue]End[/color] [color=blue]With[/color]
            Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'case G column selected,  but not a heading row[/color]
        Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]'case no G column selection[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

Alan

_.................

P.s. A version of my Workaround may be appropriate for a general solution to this ( as far as I know ) not solved problem for VBA to expand an Outline ( Grouping ), ( Open Grouped Rows ), or close them.

So here is a very simplified Code alternative to consider. This “standalone starter code” will ask for a range to be closed. There will need to be modifications if you are dealing with multiple levels, that is to say Levels greater than 2 ). Error handling, or allowing for not selecting as range will further need to be considered, But maybe it shows the general idea to get started.

The range to be closed you can select.

The range to be expanded you would normally need to select equal to a Specific Outline Group, But for this demo code it will ask you if you want to re open the group you just closed.

( I note that in this case the .Ungroup and .Group could strangely be omitted. But this was not the case previously for ma specific solution, so I leave those steps in for now as “Belts and braces” !!.)

[color=darkgreen]'[/color]
[color=blue]Sub[/color] ExpandOrCollapseAnyGroup() '
[color=darkgreen]'Collapse[/color]
[color=blue]Dim[/color] Gprng [color=blue]As[/color] Range: [color=blue]Set[/color] Gprng = Application.InputBox(prompt:="Select any range in Rows to be closed", Title:="Close Selected rows", Type:=8) '                                                                                                                                          Default:="A1",
    [color=blue]With[/color] Gprng
        .EntireRow.Hidden = [color=blue]True[/color]
    [color=blue]End[/color] [color=blue]With[/color]
[color=darkgreen]'Expand ( Re-Open )[/color]
[color=blue]Dim[/color] rwstt [color=blue]As[/color] [color=blue]Long[/color], rwstp [color=blue]As[/color] Long: [color=blue]Let[/color] rwstt = Gprng.Row: [color=blue]Let[/color] rwstp = (Gprng.Row + (Gprng.Rows.Count - 1))
[color=blue]Dim[/color] Response [color=blue]As[/color] [color=blue]Integer[/color] [color=darkgreen]'In VBA Butons "yes is 6, 7 is "no"[/color]
Response = MsgBox(prompt:="Do you want to re open rows " & rwstt & " - " & rwstp & "", Buttons:=vbYesNo, Title:="Re Open ?")    [color=darkgreen]' Displays a message box with the yes and no options.[/color]
    [color=blue]If[/color] Response = vbYes [color=blue]Then[/color]
        [color=blue]With[/color] Rows("" & rwstt & ":" & rwstp & "")
            [color=blue]If[/color] .OutlineLevel > 1 [color=blue]Then[/color] [color=darkgreen]'This prevents error if already no grouping ![/color]
            .Ungroup [color=darkgreen]'Would error if no grouping[/color]
            .EntireRow.Hidden = [color=blue]False[/color] [color=darkgreen]'Will not error if already Un Hidden[/color]
            .Group
            Else: [color=blue]End[/color] [color=blue]If[/color] [color=darkgreen]' ! case already no Grouping[/color]
        [color=blue]End[/color] [color=blue]With[/color]
    Else: [color=blue]End[/color] [color=blue]If[/color]
End [color=blue]Sub[/color]