VBA autofilter field using dynamic range

[jcooooper]

Hello all,

If I have the following line of VBA, can I make the "AutoFilter Field:=7" number dynamic?

ActiveSheet.Range("Data_column_headers").AutoFilter Field:=7, Criteria1:="="

Ideally I would place my named range in there as my columns move around.


Thanks!

 

[jcooooper]

Does that range always start in col A?
What is the named range of the column you want to filter?

The range "data_column_headers" will always start in A14 and expand rightwards.

The name of the range I want to filter is "data_portfolio_code". This column is usually in column C, though it may move. I can try to fix this column but would prefer to keep it dynamic if possible.

The criteria I want to filter is if it equals any portfolio code in the named range "data_cash_portfolio_codes"

 

[Fluff]

OK, how about

ActiveSheet.Range("Data_column_headers").AutoFilter Field:=Range("data_portfolio_code").Column, Criteria1:="="

 

[jcooooper]

OK, how about

ActiveSheet.Range("Data_column_headers").AutoFilter Field:=Range("data_portfolio_code").Column, Criteria1:="="

Yeah that works for one filter I am applying. Thanks!

For the other, is it possible to have the following:

Criteria1:="Named_range"

I've tried it but doesn't seem to be working.

 

[Fluff]

Try

Range("Data_column_headers").AutoFilter Range("data_portfolio_code").Column, Application.Transpose(Range("range1")), xlFilterValues

 

[jcooooper]

Try

Range("Data_column_headers").AutoFilter Range("data_portfolio_code").Column, Application.Transpose(Range("range1")), xlFilterValues

That works perfect and exactly what I was after, thanks so much for persevering.

Just for my own future reference, is there any way I can adapt the formula so that it doesn't equal a named range? I tried ("<>range1") but that didn't work.

Cheers!
John

 

[Fluff]

Nope, unless you are filtering on an exact match you can only use two criteria.
You might want to have a look at advanced filters instead.

 

[jcooooper]

Nope, unless you are filtering on an exact match you can only use two criteria.
You might want to have a look at advanced filters instead.

Perfect, thanks very much for your help

 

[Fluff]

You're welcome & thanks for the feedback