PDA

View Full Version : Hiding rows based on cell value - VBA



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!

georgiboy
06-22-2018, 06:36 AM
Welcome to the forum

Maybe something like the below would be easier to read:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C17")) Is Nothing Then
Select Case Range("C17").Value
Case "AAA"
Rows("21:31").Hidden = True
Case "BBB"
Rows("18:20").Hidden = True
Rows("24:31").Hidden = True
Case "CCC"
Rows("18:23").Hidden = True
Rows("26:31").Hidden = True
Case "DDD"
Rows("18:25").Hidden = True
Rows("29:31").Hidden = True
Case "EEE"
Rows("18:28").Hidden = True
Case ""
Rows("18:31").Hidden = False
End Select
End If
End If

End Sub

Hope this helps