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!

 

[Fluff]

Which named range do you want to use?
If its the named range used for the filter, will it always be the 7th column?

 

[jcooooper]

Which named range do you want to use?
If its the named range used for the filter, will it always be the 7th column?

No that's the problem, this data set always moves around....so the named range is dynamic, using the offset function.

The name of the column will always be the same though?

 

[Fluff]

Do you mean that sometimes the data starts in col A & sometimes in column E?

 

[jcooooper]

Yep the column will shift around, but its always just a single column wide (it's part of a larger data set).

 

[Fluff]

Are you saying that "Data_column_headers" is a named range 1 column wide?

 

[jcooooper]

For example. this is an extract of the data, which actually may be 30 columns wide,

Actually come to think of it I have two questions

The first, how do I filter so that portfolio code = a named range elsewhere (a list of portfolio codes). That named range is dynamic and expand and contracts.

And then the second is that column will move around, so I need a way to dynamically link to that column header to the actual filter

Sorry if this seems complicated



[TABLE="width: 806"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Portfolio Name[/TD]
[TD]Country Name[/TD]
[TD]Notional Weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GB927[/TD]
[TD]Germany[/TD]
[TD]5.2%[/TD]
[/TR]
[TR]
[TD]G6008[/TD]
[TD]Germany[/TD]
[TD]-5.2%[/TD]
[/TR]
[TR]
[TD]GB908[/TD]
[TD]United States[/TD]
[TD]0.2%[/TD]
[/TR]
[TR]
[TD]GB608[/TD]
[TD]European Union[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]GB928[/TD]
[TD]Korea (South), Republic of[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]36008[/TD]
[TD]United States[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]G6008[/TD]
[TD] [/TD]
[TD]1.4%[/TD]
[/TR]
[TR]
[TD]GB928[/TD]
[TD]United States[/TD]
[TD]-6.4%[/TD]
[/TR]
[TR]
[TD]GB928[/TD]
[TD]United States[/TD]
[TD]6.4%[/TD]
[/TR]
[TR]
[TD]I6008[/TD]
[TD]United States[/TD]
[TD]0.2%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Did you see my question in post#6?

 

[jcooooper]

Did you see my question in post#6?

ah sorry, appears we posted at the same time and I missed it.

Data_column_headers represents the top row, so portfolio code, country name, notional weight etc. I could potentially fix the row number if that's of any use.

 

[Fluff]

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