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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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



VBA Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
How about
VBA Code:
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top