VBA code for selecting from a drop down filter based on criteria in a range.

[Peter Muller]

Hi All,

I am trying to write the VBA code to do the following.

I want to hide specific rows from a Filter drop down box, based on criteria in a range in a different sheet.

Criteria in Sheet 1 (Parameters), range "B2":"B12"
For each "Y", then select that criteria in sheet 2 (Budget) "B1" - filter
For each "N", then that criteria must not be selected in Budget "B1"

Excel 2016

I get the VBA coding to work when looking at two criteria to select, but it all goes wrong when on the third criteria is “Y”, but the second was “N”

This is what I have:

Sub Select_Parameters()
'
' Select_POL Macro
'
             
    Sheets("Para").Select
    If Range("H5") = "Y" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:="=General", _
        Operator:=xlOr, Criteria2:="=POL"
   
   End If
      
    Sheets("Para").Select
    If Range("H5") = "N" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:="=General"
    
   End If
   
' Select_Rearing Macro
              
    Sheets("Para").Select
    If Range("H6") = "Y" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:=Array( _
        "General", "POL", "REARING"), Operator:=xlFilterValues
        
   End If
      
    Sheets("Para").Select
    If Range("H6") = "N" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:=Array( _
        "General", "POL"), Operator:=xlFilterValues
    
   End If
   
   
' Select_Laying Macro
              
    Sheets("Para").Select
    If Range("H7") = "Y" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:=Array( _
        "General", "POL", "REARING", "LAYING"), Operator:=xlFilterValues
        
   End If
      
    Sheets("Para").Select
    If Range("H7") = "N" Then
    Sheets("Budget").Select
    ActiveSheet.Range("$B$1:$B$2979").AutoFilter Field:=1, Criteria1:=Array( _
        "General", "POL", REARING), Operator:=xlFilterValues
    
   End If
   
End Sub

' NOTE
' Problem is when H6 is "N", I dont know how to not select when i get to H7
' 11 "Y" or "N" Criteria in Parameter Sheet, from H5 to H13

 

[Fluff]

If you put the criteria in sheet Para like

Excel 2013/2016
	H	I
5	Devon	Y
6	Dorset	Y
7	Wiltshire	N
8	Somerset	N
9	Yorkshire	N
10	Kent	Y
11	Oxfordshire	Y
12	Cumbria	N
13	Cornwall	N
Para

You could then use

Sub Fltr()
   Dim ary(1 To 9) As Variant
   Dim i As Long, j As Long
   
   With Sheets("Para")
      For i = 5 To 13
         If .Range("I" & i).Value = "Y" Then
            j = j + 1
            ary(j) = .Range("H" & i).Value
         End If
      Next i
   End With
   With Sheets("Budget")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("B:B").AutoFilter 1, ary, xlFilterValues
   End With
End Sub

 

[Peter Muller]

Thank you so much, I am so thankful for your assistance Fluff - I spent three days trying to find a workaround, all to no avail

Your code works perfectly fine!

 

[Fluff]

Glad to help & thanks for the feedback