PDA

View Full Version : [SOLVED] Hide and Unhide Columns based on Cell Value



Stealth
05-09-2018, 06:16 AM
:( 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
05-09-2018, 06:57 AM
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
05-09-2018, 03:00 PM
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
05-09-2018, 03:08 PM
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
05-09-2018, 04:04 PM
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
05-09-2018, 04:05 PM
How do I get the version 2 code from your attached file. I am new at this VBA caper.

Paul_Hossler
05-09-2018, 06:58 PM
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
05-10-2018, 02:59 PM
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
05-10-2018, 08:15 PM
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
05-11-2018, 07:30 AM
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
05-16-2018, 12:38 AM
Thanks Paul that works really well. Your help, support and understanding is really appreciated. I will mark thread as solved.