Forum Discussion
Hiding rows with multiple dropdown menu's
Dear everyone,
I have an issue with hiding rows based on multiple drowdown menu's within one sheet. The problem is that the 2nd dropdown menu is overlapping the 1st one. Please find screenshots and my code below. As can be seen, the units per property work, but the number of properties is overruled by the units. (e.g. only 2 properties should be shown here instead of 5)
The code I used (please note I am a major rookie):
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D7") = "-1 " Then
Rows("1:12").EntireRow.Hidden = False
Else
Rows("13:81").EntireRow.Hidden = True
End If
If Range("D7") = "1" Then
Rows("13:18").EntireRow.Hidden = False
Else
Rows("20:81").EntireRow.Hidden = True
End If
If Range("D7") = "2" Then
Rows("13:25").EntireRow.Hidden = False
Else
Rows("26:81").EntireRow.Hidden = True
End If
If Range("D7") = "3" Then
Rows("13:32").EntireRow.Hidden = False
Else
Rows("33:81").EntireRow.Hidden = True
End If
If Range("D7") = "4" Then
Rows("13:39").EntireRow.Hidden = False
Else
Rows("40:81").EntireRow.Hidden = True
End If
If Range("D7") = "5" Then
Rows("13:46").EntireRow.Hidden = False
Else
Rows("47:81").EntireRow.Hidden = True
End If
If Range("D7") = "6" Then
Rows("13:53").EntireRow.Hidden = False
Else
Rows("54:81").EntireRow.Hidden = True
End If
If Range("D7") = "7" Then
Rows("13:60").EntireRow.Hidden = False
Else
Rows("61:81").EntireRow.Hidden = True
End If
If Range("D7") = "8" Then
Rows("13:67").EntireRow.Hidden = False
Else
Rows("68:81").EntireRow.Hidden = True
End If
If Range("D7") = "9" Then
Rows("13:74").EntireRow.Hidden = False
Else
Rows("75:81").EntireRow.Hidden = True
End If
If Range("D7") = "10" Then
Rows("13:81").EntireRow.Hidden = False
End If
If Range("D8") = "-1" Then
Rows("1:12").EntireRow.Hidden = False
Else
Rows("13:81").EntireRow.Hidden = True
End If
If Range("D8") = "1" Then
Rows("13").EntireRow.Hidden = False
Rows("20").EntireRow.Hidden = False
Rows("27").EntireRow.Hidden = False
Rows("34").EntireRow.Hidden = False
Rows("41").EntireRow.Hidden = False
Rows("48").EntireRow.Hidden = False
Rows("55").EntireRow.Hidden = False
Rows("62").EntireRow.Hidden = False
Rows("69").EntireRow.Hidden = False
Rows("76").EntireRow.Hidden = False
Rows("19").EntireRow.Hidden = False
Rows("26").EntireRow.Hidden = False
Rows("33").EntireRow.Hidden = False
Rows("40").EntireRow.Hidden = False
Rows("47").EntireRow.Hidden = False
Rows("54").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = False
Rows("68").EntireRow.Hidden = False
Rows("75").EntireRow.Hidden = False
Rows("82").EntireRow.Hidden = False
Else
Rows("14:18").EntireRow.Hidden = True
Rows("21:25").EntireRow.Hidden = True
Rows("28:32").EntireRow.Hidden = True
Rows("35:39").EntireRow.Hidden = True
Rows("42:46").EntireRow.Hidden = True
Rows("49:53").EntireRow.Hidden = True
Rows("56:60").EntireRow.Hidden = True
Rows("63:67").EntireRow.Hidden = True
Rows("70:74").EntireRow.Hidden = True
Rows("77:81").EntireRow.Hidden = True
End If
If Range("D8") = "2" Then
Rows("13:14").EntireRow.Hidden = False
Rows("20:21").EntireRow.Hidden = False
Rows("27:28").EntireRow.Hidden = False
Rows("34:35").EntireRow.Hidden = False
Rows("41:42").EntireRow.Hidden = False
Rows("48:49").EntireRow.Hidden = False
Rows("55:56").EntireRow.Hidden = False
Rows("62:63").EntireRow.Hidden = False
Rows("69:70").EntireRow.Hidden = False
Rows("76:77").EntireRow.Hidden = False
Rows("19").EntireRow.Hidden = False
Rows("26").EntireRow.Hidden = False
Rows("33").EntireRow.Hidden = False
Rows("40").EntireRow.Hidden = False
Rows("47").EntireRow.Hidden = False
Rows("54").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = False
Rows("68").EntireRow.Hidden = False
Rows("75").EntireRow.Hidden = False
Rows("82").EntireRow.Hidden = False
Else
Rows("15:18").EntireRow.Hidden = True
Rows("22:25").EntireRow.Hidden = True
Rows("29:32").EntireRow.Hidden = True
Rows("36:39").EntireRow.Hidden = True
Rows("43:46").EntireRow.Hidden = True
Rows("50:53").EntireRow.Hidden = True
Rows("57:60").EntireRow.Hidden = True
Rows("64:67").EntireRow.Hidden = True
Rows("71:74").EntireRow.Hidden = True
Rows("78:81").EntireRow.Hidden = True
End If
If Range("D8") = "3" Then
Rows("13:15").EntireRow.Hidden = False
Rows("20:22").EntireRow.Hidden = False
Rows("27:29").EntireRow.Hidden = False
Rows("34:36").EntireRow.Hidden = False
Rows("41:43").EntireRow.Hidden = False
Rows("48:50").EntireRow.Hidden = False
Rows("55:57").EntireRow.Hidden = False
Rows("62:64").EntireRow.Hidden = False
Rows("69:71").EntireRow.Hidden = False
Rows("76:78").EntireRow.Hidden = False
Rows("19").EntireRow.Hidden = False
Rows("26").EntireRow.Hidden = False
Rows("33").EntireRow.Hidden = False
Rows("40").EntireRow.Hidden = False
Rows("47").EntireRow.Hidden = False
Rows("54").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = False
Rows("68").EntireRow.Hidden = False
Rows("75").EntireRow.Hidden = False
Rows("82").EntireRow.Hidden = False
Else
Rows("16:18").EntireRow.Hidden = True
Rows("23:25").EntireRow.Hidden = True
Rows("30:32").EntireRow.Hidden = True
Rows("37:39").EntireRow.Hidden = True
Rows("44:46").EntireRow.Hidden = True
Rows("51:53").EntireRow.Hidden = True
Rows("58:60").EntireRow.Hidden = True
Rows("65:67").EntireRow.Hidden = True
Rows("72:74").EntireRow.Hidden = True
Rows("79:81").EntireRow.Hidden = True
End If
If Range("D8") = "4" Then
Rows("13:16").EntireRow.Hidden = False
Rows("20:23").EntireRow.Hidden = False
Rows("27:30").EntireRow.Hidden = False
Rows("34:37").EntireRow.Hidden = False
Rows("41:44").EntireRow.Hidden = False
Rows("48:51").EntireRow.Hidden = False
Rows("55:58").EntireRow.Hidden = False
Rows("62:65").EntireRow.Hidden = False
Rows("69:72").EntireRow.Hidden = False
Rows("76:79").EntireRow.Hidden = False
Rows("19").EntireRow.Hidden = False
Rows("26").EntireRow.Hidden = False
Rows("33").EntireRow.Hidden = False
Rows("40").EntireRow.Hidden = False
Rows("47").EntireRow.Hidden = False
Rows("54").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = False
Rows("68").EntireRow.Hidden = False
Rows("75").EntireRow.Hidden = False
Rows("82").EntireRow.Hidden = False
Else
Rows("17:18").EntireRow.Hidden = True
Rows("24:25").EntireRow.Hidden = True
Rows("31:32").EntireRow.Hidden = True
Rows("38:39").EntireRow.Hidden = True
Rows("45:46").EntireRow.Hidden = True
Rows("52:53").EntireRow.Hidden = True
Rows("59:60").EntireRow.Hidden = True
Rows("66:67").EntireRow.Hidden = True
Rows("73:74").EntireRow.Hidden = True
Rows("80:81").EntireRow.Hidden = True
End If
If Range("D8") = "5" Then
Rows("13:17").EntireRow.Hidden = False
Rows("20:24").EntireRow.Hidden = False
Rows("27:31").EntireRow.Hidden = False
Rows("34:38").EntireRow.Hidden = False
Rows("41:45").EntireRow.Hidden = False
Rows("48:52").EntireRow.Hidden = False
Rows("55:59").EntireRow.Hidden = False
Rows("62:66").EntireRow.Hidden = False
Rows("69:73").EntireRow.Hidden = False
Rows("76:80").EntireRow.Hidden = False
Rows("19").EntireRow.Hidden = False
Rows("26").EntireRow.Hidden = False
Rows("33").EntireRow.Hidden = False
Rows("40").EntireRow.Hidden = False
Rows("47").EntireRow.Hidden = False
Rows("54").EntireRow.Hidden = False
Rows("61").EntireRow.Hidden = False
Rows("68").EntireRow.Hidden = False
Rows("75").EntireRow.Hidden = False
Rows("82").EntireRow.Hidden = False
Else
Rows("18").EntireRow.Hidden = True
Rows("25").EntireRow.Hidden = True
Rows("32").EntireRow.Hidden = True
Rows("39").EntireRow.Hidden = True
Rows("46").EntireRow.Hidden = True
Rows("53").EntireRow.Hidden = True
Rows("60").EntireRow.Hidden = True
Rows("67").EntireRow.Hidden = True
Rows("74").EntireRow.Hidden = True
Rows("81").EntireRow.Hidden = True
End If
If Range("D8") = "6" Then
Rows("13:81").EntireRow.Hidden = False
End If
If Target.Address = "$D$7" Then
'Write Code for Range("D7") here.
ElseIf Target.Address = "$D$8" Then
'Write Code for Range("D8") here.
End If
End Sub
Does anybody know how I can give the 'number of properties' priority before the units per property are taken into account?
Thx in advance,
DG
No RepliesBe the first to reply
