Forum Discussion
VBAs in excel for beginners!
Hi, thanks I've added them all but the first rule is not working it only hides 3 rows to the next rule when it should hide all questions to line 45 - all other rules are working correctly.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E5") = "No" Then
Rows("6:45").EntireRow.Hidden = True
Else
Rows("6:45").EntireRow.Hidden = False
End If
If Range("E7") = "No" Then
Rows("8:18").EntireRow.Hidden = True
Else
Rows("8:18").EntireRow.Hidden = False
End If
If Range("E20") = "No" Then
Rows("21:24").EntireRow.Hidden = True
Else
Rows("21:24").EntireRow.Hidden = False
End If
If Range("E26") = "No" Then
Rows("27:36").EntireRow.Hidden = True
Else
Rows("27:36").EntireRow.Hidden = False
End If
If Range("E38") = "No" Then
Rows("39:45").EntireRow.Hidden = True
Else
Rows("39:45").EntireRow.Hidden = False
End If
End Sub
North_Yorks Insert "Exit Sub" before the first "Else". Then it works. When E5 = No, you want to hide all. No need to check any further. Without the exit code, the macro check the next condition. It is not No, thus it unhides the rows that were hidden in the first step and so on.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E5") = "No" Then
Rows("6:45").EntireRow.Hidden = True
Exit Sub
Else
Rows("6:45").EntireRow.Hidden = False
End If
If Range("E7") = "No" Then
Rows("8:18").EntireRow.Hidden = True
Else
Rows("8:18").EntireRow.Hidden = False
End If
If Range("E20") = "No" Then
Rows("21:24").EntireRow.Hidden = True
Else
Rows("21:24").EntireRow.Hidden = False
End If
If Range("E26") = "No" Then
Rows("27:36").EntireRow.Hidden = True
Else
Rows("27:36").EntireRow.Hidden = False
End If
If Range("E38") = "No" Then
Rows("39:45").EntireRow.Hidden = True
Else
Rows("39:45").EntireRow.Hidden = False
End If
End Sub