View Full Version : Hide columns based on cell value
valvik
08-07-2012, 03:01 AM
Hi!
I am trying to hide several columns based on the value of a cell.
my code looks like this:
Private Sub Worksheet_Change(ByVal target As Range)
Dim i As Integer
For i = 1 To 100
chk_value = Cells(4, 9).Value
If chk_value = i Then
Range(Columns(i), Columns(100)).EntireColumn.Hide = True
End If
End Sub
Anyone who can spot my mistake? Currently it is no0t working at all.
Thanks!
mancubus
08-07-2012, 07:48 AM
wellcome to the forum.
you didn't word your requirement well.
so i tried to guess what it could be and came up with this...
if Range("I4").Value changes and it's less than 101 hide columns between Columns(Range("I4").value) and Columns(100)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Application.ScreenUpdating = False
If Target.Address <> "$I$4" Then Exit Sub
If Target.Value > 100 Then Exit Sub
For i = Target.Value To 100
Columns(i).EntireColumn.Hidden = True
Next
Application.ScreenUpdating = True
End Sub
valvik
08-07-2012, 08:26 AM
That worked magic! Thanks a lot!
Do you by any chance know how I can make it update it automatically when the value of "I4" changes?
And also how to unhide the columns when the value of "I4" increases.
Thanks!
mancubus
08-07-2012, 02:35 PM
you're wellcome...
copy the code to related worksheet's code module. any change in cell I4 triggers the code.
i added two lines to above procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Application.ScreenUpdating = False
If Target.Address <> "$I$4" Then Exit Sub
If Target.Value > 100 Or Target.Value < 1 Then Exit Sub
Columns("A:IV").EntireColumn.Hidden = False 'first unhide any hidden cols
For i = Target.Value To 100
If i <> Target.Column Then 'donot hide col I
Columns(i).EntireColumn.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
mancubus
08-07-2012, 02:53 PM
And also how to unhide the columns when the value of "I4" increases.
you mean hide columns only when the value of "I4" decreases, and unhide all hidden cols if the value of "I4" increases.?
mancubus
08-07-2012, 03:07 PM
if that's the case then...
Dim OldVal
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$I$4" Then Exit Sub
OldVal = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim PrevVal
Application.ScreenUpdating = False
If Target.Address <> "$I$4" Then Exit Sub
If Target.Value > 100 Or Target.Value < 1 Then Exit Sub
PrevVal = OldVal
For i = Target.Value To 100
If i <> Target.Column Then 'donot hide col I
If Target.Value < PrevVal Then
Columns(i).EntireColumn.Hidden = True
ElseIf Target.Value > PrevVal Then
Columns("A:IV").EntireColumn.Hidden = False
Else
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.