sloan85
06-22-2018, 01:31 AM
I am trying to make a form on Excel which consists of various drop down boxes and I am hiding rows based on the content of the drop down. I am completely new to VBA and have found the below online which is working for hiding rows or ranges of rows based on a cell value for "yes" or "no" but I need to hide groups of rows based on several outcomes of the drop down box but I cant get it to work.
I have a drop down with 5 options and i need it to work like the below....
if drop down in C17 shows AAA, hide rows 21 to 31
if drop down in C17 shows BBB, hide rows 18 to 20 and 24 to 31
if drop down in C17 shows CCC, hide rows 18 to 23 and 26 to 31
if drop down in C17 shows DDD, hide rows 18 to 25 and 29 to 31
if drop down in C17 shows EEE, hide rows 18 to 28
Any suggestions please? Is the below the best way to hide rows? Below is what I have used when only one per cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Rows("7").Hidden = (Target.Value = "yes")
Else
If Target.Address = "$I$13" Then
Rows("14:20").Hidden = (Target.Value = "no")
End If
End Sub
Thanks!
I have a drop down with 5 options and i need it to work like the below....
if drop down in C17 shows AAA, hide rows 21 to 31
if drop down in C17 shows BBB, hide rows 18 to 20 and 24 to 31
if drop down in C17 shows CCC, hide rows 18 to 23 and 26 to 31
if drop down in C17 shows DDD, hide rows 18 to 25 and 29 to 31
if drop down in C17 shows EEE, hide rows 18 to 28
Any suggestions please? Is the below the best way to hide rows? Below is what I have used when only one per cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Rows("7").Hidden = (Target.Value = "yes")
Else
If Target.Address = "$I$13" Then
Rows("14:20").Hidden = (Target.Value = "no")
End If
End Sub
Thanks!