Macro to hide/show different grouped columns

phil152003

Board Regular
Joined
Mar 11, 2011
Messages
89
In my spreadsheet, I have 3 sets of columns that are grouped together separately. I want to have a macro that shows/hides each group.

The problem is that because they are all separately grouped, the groups are all in row 1 in the group bar that appears at the top of the sheet (the part where if you click 1 the groups hide, and if you click 2, the groups unhide).

I found this code, which in effect clicks the "1" or "2", and so all the groups become hidden or unhidden:

Code:
Sub Hide_Everything()
'
' Hide_Everything Macro
'
'
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
End Sub
Sub Show_Everything()
'
' Show_Everything Macro
'
'
    ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
End Sub

But as I initially said, I want a button that will result in, for example, one group unhidden, while the other two remain hidden. Any suggestions? Thanks.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

phil152003

Board Regular
Joined
Mar 11, 2011
Messages
89
I've had another idea, if anyone could help me. Instead of grouping the columns, and then hiding each group, I could just instead have a macro that hides the specific columns with no need for any grouping.

I've found this code:

Code:
Sub Hide_CF()
Columns("D:K").Select
        Selection.EntireColumn.Hidden = True
End Sub
Sub Show_CF()
Columns("D:K").Select
        Selection.EntireColumn.Hidden = False
End Sub

But that leaves the range of cells selected afterwards. I also assume there is a simpler way in vba of doing exactly this, but without selecting the cells. If you can help that would be great!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,515
Instead of clicking level 1 or level 2, why don't you click the "+" or "-" that shows in the outline area? And why do you want a button to do that click for you?
 

phil152003

Board Regular
Joined
Mar 11, 2011
Messages
89
Because this set of data is going to be sent to a lot of people, some of who might not be so familiar with Excel's grouping function. I figured a simple "click here to show <set of columns 1>", for example, would be the best option to ensure that everyone who looks at this spreadsheet can work it efficiently.

Anyway, I've atually figured it out, and it works really well. Instead of using a button, I've used checkboxes instead. For anyone interested here's what I did:

Code:
Private Sub CFCheckBox_Click()
If CFCheckBox.Value = True Then
        Columns("D:K").Hidden = False
End If
If CFCheckBox.Value = False Then
        Columns("D:K").Hidden = True
End If
End Sub

I've just done one of those for each checkbox, and it works brilliantly!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,411
Messages
5,595,985
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top