Macro not working thats Trying to filter if a dropdown is use with multiple criteria

[tonywatsonhelp]

Hi Everyone,

I have the code below but its not doing what i need,
I have several dropdown boxes and im trying to get the filter to filter if the cell has a value in it but some are for the same column and this is the problem instead of filtering by both one overrules the other
so I need to know how i can make it filter on all the words if that dropdown has data in it, my code is below with notes
thatnks

Sub Make_Tech_List()


List1.AutoFilterMode = False
lr1 = List1.Cells(Rows.Count, "A").End(xlUp).Row
If lr1 <= 2 Then
lr1 = 3
End If




With List1.Range("A1:BS" & lr1)
If List2.Range("C11").Value <> "" Then ' Industry
.AutoFilter Field:=22, Criteria1:=List2.Range("C11").Value
End If

If List2.Range("C12").Value <> "" Then ' Industry ' NOTES, So i want it to filter All criteria for Field 22 not just one???
.AutoFilter Field:=22, Criteria1:=List2.Range("C12").Value
End If

If List2.Range("C13").Value <> "" Then ' Industry
.AutoFilter Field:=22, Criteria1:=List2.Range("C13").Value
End If

If List2.Range("C14").Value <> "" Then ' Industry
.AutoFilter Field:=22, Criteria1:=List2.Range("C14").Value
End If

If List2.Range("C15").Value <> "" Then ' Industry
.AutoFilter Field:=22, Criteria1:=List2.Range("C15").Value
End If



If List2.Range("C18").Value <> "" Then 'sector
.AutoFilter Field:=23, Criteria1:="=*" & List2.Range("C18").Value & "*"
End If

If List2.Range("C21").Value <> "" Then 'sub sector
.AutoFilter Field:=24, Criteria1:="=*" & List2.Range("C21").Value & "*"
End If
End With

end sub

please help if you can

Thanks

Tony

 

[Fluff]

How about

Sub Make_Tech_List()
Dim Ary As Variant

List1.AutoFilterMode = False
lr1 = List1.Cells(Rows.Count, "A").End(xlUp).Row
If lr1 <= 2 Then
lr1 = 3
End If



Ary = Application.Transpose(List2.Range("C11:C15").Value)
With List1.Range("A1:BS" & lr1)
   .AutoFilter 22, Ary, xlFilterValues
End If


If List2.Range("C18").Value <> "" Then 'sector
.AutoFilter Field:=23, Criteria1:="=*" & List2.Range("C18").Value & "*"
End If

If List2.Range("C21").Value <> "" Then 'sub sector
.AutoFilter Field:=24, Criteria1:="=*" & List2.Range("C21").Value & "*"
End If
End With

End Sub

 

[tonywatsonhelp]

Thanks you fluff,
Works perfectly
Thanks
Tony

 

[Fluff]

You're welcome & thanks for the feedback.