Hide columns based on specific cell value

**samcdavies** · 05-14-2013, 05:00 AM

Hi I've searched the forum but can't find a specific way of doing this. I'm a beginner when it comes to VBA so would appreciate the help here.

I have formula in cells C58:CJ58 - the result will either be zero or a number. I would like a code that, everytime that sheet is opened, will hide any column where the cell on row 58 is zero. Ideally I don't want this to affect other columns in the sheet, but if it does, I can sort that out.

Is this possible?

Thanks in advance!

**ragulduy** · 05-14-2013, 05:19 AM

Try this - it needs to go in the worksheet object in the visual basic editor.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
For Each cl In Range("C58:CJ58").Cells
    If cl.Value = 0 Then
        Columns(cl.Column).Hidden = True
    Else
        Columns(cl.Column).Hidden = False
    End If
Next
Application.ScreenUpdating = True
End Sub

**samcdavies** · 05-14-2013, 05:53 AM

Thankyou both. I used the first one simply becaue it was first, but they both work.

Thanks again,
Sam

**watersev** · 05-14-2013, 05:52 AM

hi samcdavies, different option

Private Sub Worksheet_Activate()
Dim rng2hide As Range, cl
Range("c58:cj58").Columns.Hidden = 0
For Each cl In Range("c58:cj58")
    If cl = 0 Then
        If rng2hide Is Nothing Then Set rng2hide = cl Else Set rng2hide = Union(rng2hide, cl)
    End If
Next
If Not rng2hide Is Nothing Then rng2hide.Columns.Hidden = 1
End Sub