Forum Discussion
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 n...
- Use the # character, so $B$1#
I know that works the cells, but I didn't know I can use that in VBA
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
print activesheet.range("d1#").rows.count
and got 12
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,
- 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- 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).
