Using VBA to Hide and Unhide Columns with Click of Button

[kskapin]

Hello!

I would like a button to be pressed that would hide specific columns. Once the button is pressed again, the columns appear. I'm extremely new to VBA, so I'm not sure of code syntax just yet.

Private Sub CommandButton1_Click()
Columns("D:G").Hidden , Columns("AF:AG").Hidden, Columns("AJ:AO").Hidden = Not Columns("A:AP").Hidden
End Sub

This is what I was attempting to use. I would like the indicated columns (D:G, AF:AG, AJ:AO) to hide upon pressing the button, then all of the columns to show again once pressed again. Ideally, the button could also be labeled to show "Hide Information" and "Show Information" correctly.

Thank you so much for all your help.

 

[wedzmer]

Thanks for this thread! Very useful indeed!

but how do we alter a code for a certain condition, say if range in H50:Z:50 is empty, then hide column button?

 

[Worf]

I'm busy this week, but will work on this as soon as possible...

 

[Worf]

Is this what you want?

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Intersect(Target, Me.Range("h50:z50")) Is Nothing
    Case False
        If Application.WorksheetFunction.CountBlank(Range("h50:z50")) = 19 Then
            Me.CommandButton1.Visible = 0
        Else
            Me.CommandButton1.Visible = 1
        End If
End Select
End Sub

 

[Learner2015]

Greetings All,

I am blown away by the capabilities of Excel VBA and am seeking to incorporate into large spreadsheets initially to hide and unhide selected columns using say 5 different buttons.
I have over 100 columns and would like to hide all columns except those within ranges linked to each button.
Please can someone show me how to set up button and write macro for the above - any assistance would be much appreciated.

 

[Worf]

Greetings and welcome to the Board

Is this what you want?

' sheet module
Private Sub CommandButton1_Click()
Me.Columns("b:j").Hidden = 1        ' columns B thru J
Me.Columns("d").Hidden = 0          ' not hidden
End Sub


Private Sub CommandButton2_Click()
Me.Columns("b:j").Hidden = 1        ' hidden
Me.Columns("f").Hidden = 0
End Sub

 

[Learner2015]

Many thanks Worf,

I tried an amended version of an earlier post to insert buttons to hide and unhide specific columns. Each button was intended to hide and unhide their own selected range of columns however, the buttons appear to be linked because their names sometimes mirror one another when pressed causing further unintended complications when clicking button after the unintnded name swap. How do I get each button to hide and unhide columns without this 'mirror' issue?
Below is the exact code I am using, any help would be great:
Private Sub CommandButton1_Click()

If CommandButton1.Caption = "Select CVR Analysis" Then
Range("A:E,H:AT,AV:CP,CR:CS,CU:DA,DE:DH,DK:DL,DP:DV").EntireColumn.Hidden = True
CommandButton1.Caption = "CVR Analysis"
Else
Range("A:E,H:AT,AV:CP,CR:CS,CU:DA,DE:DH,DK:DL,DP:DV").EntireColumn.Hidden = False
CommandButton1.Caption = "Select CVR Analysis"
End If
End Sub
Private Sub CommandButton2_Click()
If CommandButton1.Caption = "Select Programme Analysis" Then
Range("A:E,H:AA,AC:AD,AG:AJ,AV:EF").EntireColumn.Hidden = True
CommandButton1.Caption = "Programme Analysis"
Else
Range("A:E,H:AA,AC:AD,AG:AJ,AV:EF").EntireColumn.Hidden = False
CommandButton1.Caption = "Select Programme Analysis"
End If
End Sub

 

[Worf]

The buttons are linked because button 2 changes button 1:

[COLOR=#333333]Private Sub [/COLOR][COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333]_Click()[/COLOR]

[COLOR=#333333]If [/COLOR][COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "Select CVR Analysis" Then[/COLOR]
[COLOR=#333333]Range("A:E,H:AT,AV:CP,CR:CS,CU:DA,DE:DH,DK:DL,DP:DV").EntireColumn.Hidden = True[/COLOR]
[COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "CVR Analysis"[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Range("A:E,H:AT,AV:CP,CR:CS,CU:DA,DE:DH,DK:DL,DP:DV").EntireColumn.Hidden = False[/COLOR]
[COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "Select CVR Analysis"[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub
[/COLOR]
[COLOR=#333333]Private Sub[/COLOR][COLOR=#00ff00] CommandButton2[/COLOR][COLOR=#333333]_Click()[/COLOR]
[COLOR=#333333]If [/COLOR][COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "Select Programme Analysis" Then[/COLOR]
[COLOR=#333333]Range("A:E,H:AA,AC:AD,AG:AJ,AV:EF").EntireColumn.Hidden = True[/COLOR]
[COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "Programme Analysis"[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Range("A:E,H:AA,AC:AD,AG:AJ,AV:EF").EntireColumn.Hidden = False[/COLOR]
[COLOR=#ff0000]CommandButton1[/COLOR][COLOR=#333333].Caption = "Select Programme Analysis"[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Learner2015]

Cheers Worf,

I also noted that a bug occurred in commandbutton.3 which included a single column reference rather than a range - now fixed

 

[1MOUSHO]

ITS PERFECT THX
But i have problem with the charts
its hide them to, even that they not in the range
can u help me?

If you're actually using CommandButton1 (as in your example) you can go to the properties for that button and give it the caption "Hide Information". Make sure your columns of interest are showing and then use this code for the button.

Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Information" Then
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = True
  CommandButton1.Caption = "Show Information"
Else
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = False
  CommandButton1.Caption = "Hide Information"
End If
End Sub

You can also use a Forms button the same way. Let us know if that's what you really want to do.

Hope it helps.

 

[Worf]

Welcome to the Board

Hidden data is not charted, unless you toggle this option. Does the chart turn blank or does it disappear completely?