Hiding columns

[Guest]

Can I hide 5 consecutive columns (X to AA) depending on the content in
another cell (Hide if Y2=4, display otherwise) without using conditional
formatting. I tried and made everything white but then the comments remain.
Any ideas?

 

[Jason Morin]

You'd have to use a worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRng As Range
Set HideRng = Me.[X:AA]
With Target
If .Address <> "$Y$2" Then Exit Sub
If .Value = 4 Then
HideRng.EntireColumn.Hidden = True
End If
End With
End Sub

---
Right-click on the worksheet tab, click on "View Code",
and paste in the code above. Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA

 

[Guest]

Thanks Jason,
To expand my qestion a bit.
A have a workbook with 12 sheets.
If I enter the value 4 in cell D4 on sheet 1 I would like to hide columns X
to AA in sheet 4-10. Note that the name of the sheets 4-10 may change.
Is it possible by entering this 4 into D4 it automatically hides those
desired columns?
I'm quite new at visual basic so I do not know where in the VBA editor I
should paste the code.

Thanks


"Jason Morin" skrev:

You'd have to use a worksheet_change event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRng As Range
Set HideRng = Me.[X:AA]
With Target
If .Address <> "$Y$2" Then Exit Sub
If .Value = 4 Then
HideRng.EntireColumn.Hidden = True
End If
End With
End Sub

---
Right-click on the worksheet tab, click on "View Code",
and paste in the code above. Press ALT+Q to close the VBE.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I hide 5 consecutive columns (X to AA) depending on the content in
another cell (Hide if Y2=4, display otherwise) without using conditional
formatting. I tried and made everything white but then the comments remain.
Any ideas?

.