Hide columns based on cell value

[valvik]

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]

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]

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]

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]

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]

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