VBA Hide/Unhide Columns Based On Value

[bobslow21]

I am trying to write a macro to hide/unhide a column based on the value in Row 1 of that column. The range needs to look over several hundred cells.

Example if A1 = 0 then hide Column A
If A1, B1, R1 = 0 the Hide Columns A, B, R etc..

 

[JackDanIce]

This will hide all columns where there is a value of 0 in the first row:

Sub Macro1()
 
Dim i As Long
 
Application.ScreenUpdating = False
 
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Cells(1, i) = 0 Then Cells(1, i).EntireColumn.Hidden = True
Next i
 
Application.ScreenUpdating = True
 
End Sub

 

[bobslow21]

This will hide all columns where there is a value of 0 in the first row:

Sub Macro1()
 
Dim i As Long
 
Application.ScreenUpdating = False
 
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
    If Cells(1, i) = 0 Then Cells(1, i).EntireColumn.Hidden = True
Next i
 
Application.ScreenUpdating = True
 
End Sub

Perfect, except how do I have it unhide when its not zero. Will be assigning the macro to a button so every time I hit the button it hides all 0 and unhides everything else??

 

[Rick Rothstein]

Perfect, except how do I have it unhide when its not zero. Will be assigning the macro to a button so every time I hit the button it hides all 0 and unhides everything else??

You could unhide all the hidden columns before running your loop so that it will just hide or re-hide your zeroes...

Columns.Hidden = False