VBA Code - Hiding rows

[Kritty]

Hey,

I'm new to VBA and am trying to implement some code into my spreadsheets. At the moment I am trying to hide rows based on a value in a specific cell (K21).

Basically what I am trying to achieve is the following
If cell K21 = 1, hide rows 36 to 46
If cell K21 = 2, hide rows 40 to 46
If cell K21 = 3, hide rows 44 to 46
If cell K21 = 4, hide rows 47 to 47

At the moment my code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$21" Then
If Target.Value = 1 Then Rows("36:46").EntireRow.Hidden = True
If Target.Value = 2 Then Rows("40:46").EntireRow.Hidden = True
If Target.Value = 3 Then Rows("44:46").EntireRow.Hidden = True
If Target.Value = 4 Then Rows("47:47").EntireRow.Hidden = True
End If
End Sub


The above code works only if I set the value of cell K21 once. If I try and change the value (lets say from 2 to 3) it will not hide the correct rows. I think it has something to do with my ranges overlapping?

Any help would be much appreciated

Thanks

 

[Michael M]

Maybe like this.....rows need to be Unhidden before each run !!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$21" Then
Cells.EntireRow.Hidden = False
If Target.Value = 1 Then Rows("36:46").EntireRow.Hidden = True
If Target.Value = 2 Then Rows("40:46").EntireRow.Hidden = True
If Target.Value = 3 Then Rows("44:46").EntireRow.Hidden = True
If Target.Value = 4 Then Rows("47:47").EntireRow.Hidden = True
End If
End Sub

 

[Kritty]

Maybe like this.....rows need to be Unhidden before each run !!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$K$21" Then
Cells.EntireRow.Hidden = False
If Target.Value = 1 Then Rows("36:46").EntireRow.Hidden = True
If Target.Value = 2 Then Rows("40:46").EntireRow.Hidden = True
If Target.Value = 3 Then Rows("44:46").EntireRow.Hidden = True
If Target.Value = 4 Then Rows("47:47").EntireRow.Hidden = True
End If
End Sub

Thanks for that! Just tried it and it works perfectly

 

[Michael M]

glad to help and thx for the feedback...