VBA - how to hide columns in various sheets according to criteria

[davey4444]

Hi all,

I have 3 worksheets with different values in row 5 (values can only be a number under 10) and I would like to cycle through the sheets and hide any columns in E:U where the value in the corresponding row 5 is not 0 or 1.
i.e if cell E5 = 0, do not hide and if cells F5 and G5 = 2 then hide them.

I have looked through various examples of hiding columns/rows according to specific criteria however nothing seems to exactly match what I need and I'm not of the level to manipulate the code to my needs.

Thanks in advance.

 

[NeonRedSharpie]

Sub hideSheets()



    For x = 1 To Worksheets.Count
    With Sheets(x)
        If .Range("E5") = 0 Then
            .Visible = True
        ElseIf .Range("F5") = 2 And .Range("G5") = 2 Then
            .Visible = False
        End If
    End With
    Next x




End Sub

This assumes that E5 take priority over F5=G5=2

 

[davey4444]

Hi,

Thanks for the reply but won't this hide the entire sheet? I just want to hide the columns which do not contain 0 or 1.

Thanks.

 

[NeonRedSharpie]

Yup, you're right. I read your question wrong, sorry about that.

Sub hideSheets()



    For x = 1 To Worksheets.Count
    With Sheets(x)
        For y = 5 To 21
            If .Cells(5, y).value = 0 Or .Cells(5, y).value = 1 Then
                .Cells(5, y).EntireColumn.Hidden = False
            Else
                .Cells(5, y).EntireColumn.Hidden = True
            End If
        Next y
    End With
    Next x




End Sub

 

[davey4444]

Ah, that's perfect. Thanks for your help, I appreciate it.

 

[Rick Rothstein]

You can also do this with one line of code (albeit a long one)...

Sub HideColumns()
  Range(Replace(Application.Trim(Join(Evaluate("IF(E5:U5>1,ADDRESS(1,COLUMN(E5:U5),4)&"":""&ADDRESS(1,COLUMN(E5:U5),4),"""")"))), " ", ",")).EntireColumn.Hidden = True
End Sub