Conditionally Hiding Columns in Excel

[ajhess]

Can someone please tell me what I am doing wrong? I want to hide columns based on the values found in 2 different cells, but I can't seem to get it right. Below is my current attempt... thanks for looking.

Sub Hide0DollarColumns()
Dim GLvalue As String
Dim RVU As String

For Each r In Range("L10:BA10")
    If (r.Value = 0 Or r.Value = "") Then
        GLvalue = "Hide"
    Else: GLvalue = "UnHide"
    End If
Next

For Each r In Range("L10:BA10")
    If (r.Offset(5, 0).Value = "OOR" Or r.Offset(5, 0).Value = "(blank)" Or r.Offset(5, 0).Value = "" Or IsNull(r.Offset(5, 0)) = True) Then
        RVU = "UnHide"
    Else: RVU = "Hide"
    End If
Next

For Each r In Range("L10:BA10")
    If (GLvalue = "Hide" And RVU = "Hide") Then
        r.EntireColumn.Hidden = True
        Else: r.EntireColumn.Hidden = False
    End If
Next

 

[SkipVought]

Hi,

What does, "can't seem to get it right to" actually mean?

Does the wrong column(s) get hidden or not or do no columns get hidden?

Please tell me an column/cell instance that is not right, including the formula and/or value in rows 10 and 15. Please be very explicit.

 

[ajhess]

With this code nothing is being hidden. So for example, Cell N10 is NULL (so it meets my 1st "Hide" criteria), and N15 is "AGENCY" (so it meets my 2nd "Hide" criteria). Yet column N remains visible after running my code. Does that help?

 

[SkipVought]

you never actually hide or unhide except in column BA.

 

[SkipVought]

Option Explicit

Sub Hide0DollarColumns()
    Dim GLvalue As String
    Dim RVU As String
    Dim r As Range
    
    For Each r In Range("L10:BA10")
        If (r.Value = 0 Or r.Value = "") Then
            GLvalue = "Hide"
        Else: GLvalue = "UnHide"
        End If
    
        If (r.Offset(5, 0).Value = "OOR" Or r.Offset(5, 0).Value = "(blank)" Or r.Offset(5, 0).Value = "" Or IsNull(r.Offset(5, 0)) = True) Then
            RVU = "UnHide"
        Else: RVU = "Hide"
        End If
    
        If (GLvalue = "Hide" And RVU = "Hide") Then
            r.EntireColumn.Hidden = True
            Else: r.EntireColumn.Hidden = False
        End If
    Next
End Sub

 

[ajhess]

Thanks SkipVought! That worked great.. appreciated