Advanced Filter Creating Named Range?

[Simon Lloyd]

Hi All, Lately i have been using Advanced Filter alot both at worksheet level and in code and i have just noticed (yeah observant 'aint I?) that when this action is performed Excel creates a named range called Extract but it's only for the destination cell of the filtered list any ideas why?

Also if you the perform the action again to another location it redefines the named range any ideas how this is performed? I am just interested to know if it's possible to work with this inbuilt feature in VBA for referencing Extract but referring to the entire copied range (just makes your code neater and shorter if referring to named ranges).

I have also noticed that you can reference that named range in a cell using =EXTRACT but of course only gives you the result of the single cell.

[Jan Karel Pieterse]

Excel handles the creating and modifying of range names like these. There are more functions that cause range names to surface: Autofilter, Custom views, print titles, print area, External data, ...

You could refer to the entire Extract range like this:

Worksheets("Foo").Range("Extract").CurrentRegion

[Simon Lloyd]

Jan, thanks very much for the reply!

[Simon Lloyd]

Jan, is it possible to refer to the the "CurrentRegion" at worksheet level when using Extract? i understand that CurrentRegion is a vba term but was just wondering if something similar can be used in a worksheet formula?

[Jan Karel Pieterse]

No, you'd have to define a dynamic range name to achieve that I think.