Hiding/Showing every other column Macro Code

[lbstateguy]

Hi there,
I have a spreadsheet where I want to hide/show empty columns. Problem is that I have everyother column hidden already. I wrote a code that works great, but when I run a macro that adds columns (hidden and showing), the new macro does not work. Can anyone help?

This is the code I have

Sub HideCol()
    Dim cl As Range, rTest As Range
    Set rTest = Range("F75:AH75", Range("F75:AH75").End(xlToRight))
    For Each cl In rTest
        If Not cl.Value > 0 Then
            cl.EntireColumn.Hidden = True
        End If
    Next cl
End Sub
Sub UnHideCols()
Dim cl As Range
For Each cl In Sheets("Budget").Range("F75, H75, J75, L75, N75, P75, R75, T75, V75, X75, Z75, AB75, AD75, AF75, AH75, AJ75, AK75, AL75, AM75")
  If UCase(cl) = "0" Then
      cl.EntireColumn.Hidden = False
  Else: cl.EntireColumn.Hidden = False
  End If
Next cl
End Sub
[endcode]

 

[venkat1926]

normally this code
Set rTest = Range("F75:AH75", Range("F75:AH75").End(xlToRight)) should work if there are no blanks in rows.

try this more general code


set rtest=range(range("A1:A11"),cells(1,columns.Count).end(xltoleft).end(xldown))
modify the cells addresses to suit you

 

[lbstateguy]

Hi there,
Thanks for your insight. The macro I have works great but the problem I run into occurs after I run another macro to add (not hide/show) columns. I get an error on the following line:

Sub HideCol()
Dim cl As Range, rTest As Range
Set rTest = Range("F75:AZ75", Range("F75:AZ75").End(xlToRight))
For Each cl In rTest
If Not cl.Value > 0 Then
cl.EntireColumn.Hidden = True
End If
Next cl
End Sub

Can you help me clear this error?

Thank you!

 

[lbstateguy]

Hi there,
I figured out what the problem was. Sheet was set to protect itself after the first macro ran. I had to add a code to unprotect the sheet before 2nd macro ran.

Thanks!