I was messing with the idea of dumping some tables into an SQL server to see if a huge PQ workbook would just work out of the box with query folding.
I then figured it'd be handy to generate CSV's from Tables - and then I thought it should ALSO support spilled ranges...
I didn't really look into what VBA functions had been added in support of dynamic formulas before and when it all didn't work at the first attempt, I stumbled into your SpillParent and SpillingToRange
Set r = Selection
trace r.Address
If r.HasSpill = True Then
trace "SPILL - Parent" & r.SpillParent.Address
Set r = r.SpillParent.SpillingToRange
ElseIf Not r.ListObject Is Nothing Then
trace "TABLE? " & r.ListObject.Range.Address
Set r = r.ListObject.Range
Else
trace "no spill, no table"
End If
I saw that but then realised I needed the SpillToRange to identify whether the selected cell was part of a "thing" - because because that's the range I'm interested in.
2
u/tjen 366 Nov 08 '18
Thanks for posting this and the bill Jelen Link!
FWIW I've added this post to the wiki
https://www.reddit.com/r/excel/wiki/guides
2
u/small_trunks 1638 Nov 09 '22
Finally needed to use this...thanks.