Forum Discussion

GregGann's avatar
GregGann
Copper Contributor
Mar 02, 2022
Solved

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

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?

6 Replies

    • GregGann's avatar
      GregGann
      Copper Contributor
      I know that works the cells, but I didn't know I can use that in VBA
      • mtarler's avatar
        mtarler
        Silver Contributor
        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

Resources