Hide and Unhide Columns based on Cell Value

:( 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.

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

Code:

---

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

---

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.

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

Quote:

---

Originally Posted by 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

---

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

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

Attachment 22199

Same for the ThisWorkbook code

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

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

Quote:

---

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

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

Code:

---

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

---

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