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.

 

[1MOUSHO]

The chart turn blank
thanks for the help

 

[Worf]

Chart tools>design>data>select data>click “hidden and empty cells” button>check the “show data in hidden rows and columns” option.

Does this solve the problem?

 

[1MOUSHO]

Perfect
thx

 

[Worf]

You are welcome.

 

[Antonio Manuel]

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.

Hello HalfAce, you said that is possible to use this command as in button Form but how can you include the Hide Information in the caption.

I would like to include this button on the PERSONAL.XLSB book for not to save my excel file as binary for macros.

Thanks,

 

[Antonio Manuel]

Thanks Trevor.

Once I made a single button to hide and to show in a macro like yours, but now I can't find the answer that why I was asking.

Thanks.