Group/Ungroup Macro

[fedas18]

Hi Everyone,

I am trying to write a macro that groups certain cells, 5:6 and 9:25. I then plan on putting a button to the right that says "show". WHen you click the button (run the macro), it will ungroup the column, if you click it again, it will group them.

Is this possible?

Thanks,
Evan

 

[repairman615]

Hello,

I am unsure if this is correct for you, this will show/hide entire rows 5:6 and 9:25

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> Trigger <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> LevelShow()<br><br>    <SPAN style="color:#00007F">If</SPAN> Trigger = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Sheets(1).Outline.ShowLevels rowlevels:=3<br>        Trigger = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        Sheets(1).Outline.ShowLevels rowlevels:=1<br>        Trigger = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

I tested this on a new sheet and the above macro will not work if the groups are not made prior to using the above macro.

However, manually create your grouped rows first and the above posted macro should work...or use this macro one time:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetGroup()<br><br>    Rows("5:6").Rows.Group<br>    Rows("9:25").Rows.Group<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[fedas18]

Thanks for getting back to me.

I grouped the columns and then used your first macro but it did not work

Wouldn't I need to specify the columns in the macro that I want to be grouped?



Right now I have columns 5:6 and 9:26 grouped. When I press this macro I wish that the columns expand and contract. Does that explain further?

Thanks for all of your help!

 

[repairman615]

Do you mean columns E:F and I:Y ?

 

[fedas18]

I'm sorry. I meant to type rows before

The horizonal lines I am trying to group

 

[repairman615]

I see. I am using excel 2007. It does work for me. Will you please test on a new workbook to see if it will work for you. Here are the steps:

1. Open a New workbook.
2. Alt + F11 Opens VBEditor
3. Alt - I - M Keystorkes to insert module
4. paste in both macros...make sure you also have the Public Trigger as Boolean line
5. close the editor
6. now insert a button and assign LevelShow macro to it
7. Alt +f8
8. run GetGroup
9. Last, press the button a few times.

any change?

 

[fedas18]

you the man. thank you!

what is that first line "Public Trigger as Boolean line "

 

[repairman615]

It is a public variable. This means that all your procedures could use it... I am not sure if it the best method here. I could be wrong but I think using public it stores the value for the next time the macro is run. this "remembers" whether the rows are hide/unhide.

I hope the discription is basically right..

Glad it worked,
Jeff

 

[mail2khalsa]

Hello Repairman615,

Need some help... my query is slightly different to fedas18, thought you can help me with it..

I have got data in more than 1800 rows and applied 35 grouping's to it (rows count isn't same for these grouping's)

Now, I want to ungroup one at a time (of my choice) of 35 groups while sheet remain protected all the time...

If need be, i can put a number "1" of the selected column "A" above these grouping to ungroup specific group only

any advise on this would be greatly appreciated...




Thanks
mail2khalsa

Hello,

I am unsure if this is correct for you, this will show/hide entire rows 5:6 and 9:25

Public Trigger As Boolean

Sub LevelShow()

If Trigger = False Then
Sheets(1).Outline.ShowLevels rowlevels:=3
Trigger = True
Else
Sheets(1).Outline.ShowLevels rowlevels:=1
Trigger = False
End If

End Sub