Hide rows based on dropdown

[Dcata]

Hi Guys!

I have a dropdown with a list of 5 different options.

Is there a way that I can hide all rows between 2-70 that don't have the dropdown value I have selected in column a?

e.g. for each row between 2-70, if column A <> A1 (this is where my dropdown value is) then row = hidden

Rows 2-70 completely change every week.

Cheers!

 

[Fluff]

This needs to go in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim r As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      For r = 2 To 70
         Rows(r).Hidden = Range("A" & r).Value <> Target.Value
      Next r
   End If
End Sub

 

[Dcata]

Perfect!

Thanks for helping out Fluff. Is there a way to modify that to say if dropdown = " " then display everything?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim r As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "A1" Then
      If Target.Value = " " Then
         Rows("2:70").Hidden = False
      Else
         For r = 2 To 70
            Rows(r).Hidden = Range("A" & r).Value <> Target.Value
         Next r
      End If
   End If
End Sub

 

[Dcata]

Nice 1. cheers!

 

[Fluff]

Glad to help & thanks for the feedback