Filter range based on list if cell Drop Down is AM

[hajiali]

Hello I have a really difficult Filter request

My range to be Filtered is A3:Q500

C1 is Data Validated with List AM, PM, BOTH. (Users Will selected one)

If C1="AM" I would like the Range be filtered to show only AM TIMES.

The AM TIMES and PM TIMES are in NumberLetter Format The Filtered range are also in this format. I have a List of All the possible AM Times in Range AA3:AA42 and PM TIMES in AB3:AB42. The filtered range might not consist of all the possible variation but will have most.

in other words If C1="AM" to Filter based on list in Range AA3:AA42. If C1="PM" to Filter based on list in Range AB3:AB42, if C1="BOTH" show all data

the Filter column would be column 5

 

[TheMohitDembla]

Hi, you might want to have a look at this Creating a Drop Down Filter to Extract Data Based on Selection

 

[hajiali]

Thanks for the quick reply. I was looking for more of a VBA approach

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant
   
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C1" Then
      If Target.Value = "BOTH" Then
         Me.Range("A3:Q3").AutoFilter
         Exit Sub
      ElseIf Target.Value = "AM" Then
         Ary = Application.Transpose(Range("AA3:AA42").Value)
      ElseIf Target.Value = "PM" Then
         Ary = Application.Transpose(Range("AB3:AB42").Value)
      End If
      Me.Range("A3:Q3").AutoFilter 5, Ary, xlFilterValues
   End If
End Sub

 

[hajiali]

Thanks Fluff for the code. If the sheet is protected and I need the above code to run. where or how I would go about inputting this

Me.Unprotect Password:="Password"

I have times to place this in the code but it does not seem to unprotect sheet before the code is ran.

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C1" Then
      Me.Unprotect "Password"
      If Target.Value = "BOTH" Then
         Me.Range("A3:Q3").AutoFilter
         Me.Protect "Password"
         Exit Sub
      ElseIf Target.Value = "AM" Then
         Ary = Application.Transpose(Range("AA3:AA42").Value)
      ElseIf Target.Value = "PM" Then
         Ary = Application.Transpose(Range("AB3:AB42").Value)
      End If
      Me.Range("A3:Q3").AutoFilter 5, Ary, xlFilterValues
      Me.Protect "Password"
   End If
End Sub

 

[hajiali]

I still get the protected sheet error not sure why

 

[Fluff]

What is the exact error & when do you get it?

 

[hajiali]

Error "The Cell or Chart you're trying to change is on a protected sheet. to make a change, unprotect sheet. You might be requested to enter a password.

it occurs when I try to change C1 Value

 

[hajiali]

Figured it out C1 was locked sorry