Thread
:
Hide colums based on a value in cell A1 (refered to the number in thefirst row of each column)
View Single Post
#
2
Posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
Posts: 3,872
Hide colums based on a value in cell A1 (refered to the number in the first row of each column)
Hi,
Am Fri, 11 Mar 2016 10:57:29 -0800 (PST) schrieb
:
In Cell A1 I have a selectionbox with 0, 1, 2, 3, etc..
A1 B1 C1 D1 E1 E1 F1...... till last column
box 1 2 1 1 2 3
When select in Cell A1 the number 2 then exclusive column C and E all others (except the basis column A) should be hide (this should be an automatic action after selecting/changing the value in cell A1).
When click on (= activate) Cell A2 then automatic fill in cell A1 the number 0. This value means unhide everything automatic.
try in the code module of the expected sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$1" Then Exit Sub
Dim i As Long
'Modify here the number of columns
For i = 2 To 10
If Cells(1, i) < Target Then
Columns(i).Hidden = True
End If
Next
If Target = 0 Then ActiveSheet.UsedRange.EntireColumn.Hidden = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address < "$A$2" Then Exit Sub
Range("A1") = 0
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply With Quote
Claus Busch
View Public Profile
Find all posts by Claus Busch