Hide and Unhide Columns based on Cell Value

[Stealth]

I am trying to solve a problem with Hiding Un-Hiding Columns when a Cell Value = 1.

I have an formula in my Spread Sheet that can result in any Cell in the Range "BF9:BF35" to = "1" and when this occurs I need Column "Q" and Column "AS" to Un-Hide.

How can I acheive this with VBA.

[Paul_Hossler]

Put these event handlers into the code page of the worksheet and see

Option Explicit

Private Sub Worksheet_Calculate()
    Dim i As Long
    Application.ScreenUpdating = False
    Columns("Q:Q").Hidden = False
    Columns("AS:As").Hidden = False
    For i = 9 To 35
        If Range("BF" & i).Value = 1 Then
            Columns("Q:Q").Hidden = True
            Columns("AS:As").Hidden = True
            Exit For
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    
    If Intersect(Target, Range("BF9:BF35")) Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Columns("Q:Q").Hidden = False
    Columns("AS:As").Hidden = False
    
    For i = 9 To 35
        If Range("BF" & i).Value = 1 Then
            Columns("Q:Q").Hidden = True
            Columns("AS:As").Hidden = True
            Exit For
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

[Stealth]

Thanks Paul, I inserted this into Worksheet and get the following error: "Unable to set hidden property of the range class" and I need to Un-Hide Columns when BF9:BF35 cell value is 1.

[Paul_Hossler]

I had the True/False reversed

Don't know about the error - I don't get it

Try ver 2 -- I added a WB Open event to calculate sheet 1 to make sure it initializes

You might have to change the sheet name in the sub in your workbook

[Stealth]

I had the True/False reversed

Don't know about the error - I don't get it

Try ver 2 -- I added a WB Open event to calculate sheet 1 to make sure it initializes

You might have to change the sheet name in the sub in your workbook

[Stealth]

How do I get the version 2 code from your attached file. I am new at this VBA caper.

[Paul_Hossler]

First save my revised attachment and see if it works the way you want

Then copy and paste the code from my Sheet1 to the right worksheet in your workbook

Capture.JPG

Same for the ThisWorkbook code

[Stealth]

Hi Paul, had time to go through your post last night and I am pleased to say that it works perfectly. I am using version 1. Thanks for your time and understanding.

Bill

[Stealth]

Hi Paul, Have changed to your version 2. Can this be modified to Hide/Un-Hide multiple Columnns from Multiple Cell References.

ie: Range (AF9:AI35) if any cell in AF9:AF35=1 Un-Hide Columns Q & AS, if AG9:AG35=1 Un-Hide R & AT, if AH9:AH35=1 Un-Hide S & AU and if AI9:AI35=1 Un-Hide Columns T & AV

Your modified Version 2 attached.

Bill

[Paul_Hossler]

This is a less redundant way (in ver 4) but a more straight-forward way is in ver 3

Option Explicit

'If BF=1 Un-Hide Columns Q and AS
'If BG=1 Un-Hide Columns R and AT
'If BH=1 Un-Hide Columns S and AU
'If BI=1 Un-Hide Columns T and AV
'Q = 17
'AS = 45 = Q + 28
'BF = 58 = Q + 41

Private Sub Worksheet_Calculate()
    Dim iRow As Long, iCol As Long
    Application.ScreenUpdating = False
    For iCol = 17 To 20
        Columns(iCol).Hidden = True
        Columns(iCol + 28).Hidden = True
        
        For iRow = 9 To 35
            If Cells(iRow, iCol + 41).Value = 1 Then
                Columns(iCol).Hidden = False
                Columns(iCol + 28).Hidden = False
                Exit For
            End If
        Next iRow
    Next iCol
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    
    If Intersect(Target, Cells(9, 58).Resize(27, 4)) Is Nothing Then Exit Sub
    
    Call Worksheet_Calculate
End Sub

[Stealth]

Thanks Paul that works really well. Your help, support and understanding is really appreciated. I will mark thread as solved.