Hide Rows based on 2 drop-down list

[kshitij_dch]

Hello Team ,

I am working on a file where I have 2 Drop Down List in Cell C4 and C5 Respectively

C4 user has 2 options to select ( Win , Loose)
C5 user has 2 options to select ( Make / Wake)

I am struggling to hide rows based on user selection , lets say

If user selects "Win" in C4 and "Make" in C5 than

I want to hide rows ( 4:10 ) and ( 12: 15)

and if user selects "Loose" in C4 and "Make" in C5 than

I want to hide rows (15:17) and (12:15)

Can i do it with VBA??

 

[Fluff]

Can i do it with VBA??

Yes it's possible, but we would need to know what rows to hide for every combination.

 

[kshitij_dch]

Yes it's possible, but we would need to know what rows to hide for every combination.

To be precised

for WIN rows 4 to 10
for Loose rows 15 to 17
for Make rows 12 to 15
for Wake rows 20 to 23

 

[Trixterz]

Is it a Form Control or Active X drop down list?

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C4:C5")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "C4"
            Rows("4:10").Hidden = Target.Value = "Win"
            Rows("15:17").Hidden = Target.Value = "Loose"
         Case "C5"
            Rows("12:15").Hidden = Target.Value = "Make"
            Rows("20:23").Hidden = Target.Value = "Wake"
      End Select
   End If
End Sub

 

[kshitij_dch]

Is it a Form Control or Active X drop down list?

it is a simple drp down list created from excel Data Validation option

 

[kshitij_dch]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C4:C5")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "C4"
            Rows("4:10").Hidden = Target.Value = "Win"
            Rows("15:17").Hidden = Target.Value = "Loose"
         Case "C5"
            Rows("12:15").Hidden = Target.Value = "Make"
            Rows("20:23").Hidden = Target.Value = "Wake"
      End Select
   End If
End Sub

Thank you Fluff , if worked perfectly !!

 

[kshitij_dch]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C4:C5")) Is Nothing Then
      Select Case Target.Address(0, 0)
         Case "C4"
            Rows("4:10").Hidden = Target.Value = "Win"
            Rows("15:17").Hidden = Target.Value = "Loose"
         Case "C5"
            Rows("12:15").Hidden = Target.Value = "Make"
            Rows("20:23").Hidden = Target.Value = "Wake"
      End Select
   End If
End Sub

When I protect the sheet and perform the action , Error Pops "Unable to set hidden property of the range class", basically protection of sheet is necessary to avoid user changes to the sheet

 

[Fluff]

In that case you need to unprotect the sheet like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C4:C5")) Is Nothing Then
      Me.Unprotect "abc"
      Select Case Target.Address(0, 0)
         Case "C4"
            Rows("4:10").Hidden = Target.Value = "Win"
            Rows("15:17").Hidden = Target.Value = "Loose"
         Case "C5"
            Rows("12:15").Hidden = Target.Value = "Make"
            Rows("20:23").Hidden = Target.Value = "Wake"
      End Select
   End If
   Me.Protect "abc"
End Sub

Change the abc to whatever your password is.

 

[kshitij_dch]

In that case you need to unprotect the sheet like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C4:C5")) Is Nothing Then
      Me.Unprotect "abc"
      Select Case Target.Address(0, 0)
         Case "C4"
            Rows("4:10").Hidden = Target.Value = "Win"
            Rows("15:17").Hidden = Target.Value = "Loose"
         Case "C5"
            Rows("12:15").Hidden = Target.Value = "Make"
            Rows("20:23").Hidden = Target.Value = "Wake"
      End Select
   End If
   Me.Protect "abc"
End Sub

Change the abc to whatever your password is.

Thank you for all your Help!!