How do I access #SPILL area from FILTER() result in VBA?

[GregGann]

If I use FILTER() function cell $B$1, how do I target or select the entire spill range from VBA?

There is a range or table. It is outlined in blue. The blue outline updates dynamically with the number of lines returned as the FILTER() arguments are changed. 

There is no explicit variable in the Name Manager (as happens when tables and print areas are defined, for example).

In the docs.microsoft.com Office VBA references, there are the FILTER and FILTERS objects. Both then refer to AUTOFILTER, so I am unsure if this will also apply to the results generated by FILTER()

Are there any ideas or suggestions?

[#2 · Accepted solution]

Use the # character, so $B$1#

[#3]

I know that works the cells, but I didn't know I can use that in VBA

[#4]

I tested it and it worked for me. For example I did this in the Immediate window where D1 has a spill function:
print activesheet.range("d1#").rows.count
and got 12

[#5]

@mtarler 

I am having better success than I hope for. Thanks

Range("B6:Q6").Select
Selection.Copy
Range("B6#").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

,

[#6]

FYI, it is much slower if you .select for everything. You can and should just
Range([source]).copy ([Destination])
also I found but didn't play with or test, Range has a couple interesting properties:
SpillingToRange and SpillParent
might be interesting to check them out

[#7]

I believe there is also a HasSpill property that you can test, as attempting to reference a non-existent spill range using spilltorange will generate a run time error (not sure if the Range("B6#") reference will throw an error if there's no spill range).