PDA

View Full Version : VBA Auto Filter



moni_tm
10-18-2017, 06:37 AM
20691

Good Afternoon,

I would like to setup a macro to filter Column A based on up to 10 criteria on the same page. I have attached a sample document with some code already in that does not work correctly as it returns nothing.


Sub AutofilterGenie()
Dim rg As Range
Set rg = Range("A3:A10000") 'Range to filter
Worksheets("SHEET1").Select
rg.AutoFilter 'Remove any existing AutoFilter
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)) 'All the data in column A
rg.AutoFilter Field:=1, Criteria1:=Array([F2], [G2], [H2], [I2], [J2], [K2], [L2], [M2], [N2], [O2]), Operator:=xlFilterValues 'cells to look in and filter based on value
End Sub

Any help is greatly appreciated.

Tom

Paul_Hossler
10-18-2017, 07:16 AM
Making the values strings seems to work for me





rg.AutoFilter Field:=1, Criteria1:=Array( _
CStr([F2]), CStr([G2]), CStr([H2]), _
CStr([I2]), CStr([J2]), CStr([K2]), _
CStr([L2]), CStr([M2]), CStr([N2]), CStr([O2])), _
Operator:=xlFilterValues

moni_tm
10-18-2017, 07:26 AM
Paul, thank you, new to coding - worked a treat.

Tom

Paul_Hossler
10-18-2017, 07:35 AM
No problem

Item 3 in my sig will tell you how to mark this Solved

Come back if you have more questions

moni_tm
10-18-2017, 07:48 AM
Will do Paul,

Can I ask for one more piece of information, how do I reference another worksheet cell for the a single criteria?


Sub Vision_Filter()
Dim rg As Range
Set rg = Range("A2") 'Range to filter
Worksheets("Vision").Select
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp)) 'All the data in column A

rg.AutoFilter Field:=1, Criteria1:=Worksheets("Opera")Range([D2]) ,Operator:=xlFilterValues

End Sub

I know -
rg.AutoFilter Field:=1, Criteria1:=Worksheets("Opera")Range([D2]) ,Operator:=xlFilterValues - is incorrect

Tom

Paul_Hossler
10-18-2017, 07:53 AM
Top of my head ...




rg.AutoFilter Field:=1, Criteria1:=CSTR(Worksheets("Opera")DOTRange(QUOTED2QUOTE)) ,Operator:=xlFilterValues

or

rg.AutoFilter Field:=1, Criteria1:=CStr(Worksheets("Opera").Range("D2").Value) ,Operator:=xlFilterValues