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

22199

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.