Hiding and Unhide Columns Based on Displayed Columns

[panorama414]

I have a spreadsheet that contains the following groupings and columns:

• January thru December groupings of 3 columns (Actuals, Forecast, and Variance) per month
• Quarter 1 thru Quarter 4 groupings of 3 columns (Actuals, Forecast, and Variance) per quarter
• The Quarter columns will only display when the corresponding monthly columns are hidden (i.e. Quarter 1 columns will display when January, February, March columns are collapsed, Quarter 2 columns will display when April, May, and June columns are collapsed, etc.).

Below is example of how the columns are set up in the spreadsheet:

----Jan--------- Feb---------Mar-------- Q1
Act For Var Act For Var Act For Var Act For Var

I have 5 buttons (Show/Hide Target & Variance, Show/Hide Q1, Show/Hide Q2, Show/Hide Q3, and Show/Hide Q4)

I need help in writing a macro for the Show/Hide Target & Variance button. When I click on the button, I want it to check to see whether the Target and Variance columns are displaying. If it is, then collapse those columns. If it is not, then display those columns. The macro needs to check whether the monthly columns or the quarter columns are showing and hide/unhide the target and variance columns accordingly.

 

[WinteE]

To hide/unhide columns based on criteria you could do as follows :

- Add a hidden row at the top of the sheet
- In this row you add "1" where the column should be displayed, otherwise empty
You can fill the 1's with a formula (based on validation list) or with a macro (buttons)
- Add the macro below to your sheet (you need to change the Range) :

For Each c in Range("A1:A12")
  If c.Value = 1 Then
    c.EntireColumn.Hidden = True
  Else
    c.EntireColumn.Hidden = False
  End If
Next c

Success