Forum Discussion
Range.SpillParent
Quick question. The VBA Range/Cell object has a .SpillParent property which allows one to find back to the top left corner (TLC) of a Spill from any cell within the Spill. That is, I can point the Range into the middle of a Spill and .SpillParent will give me the TLC.
Does anyone know whether there is corresponding functionality available thru regular Excel functions?
This task is similar to obtaining the current region with worksheet functions.
I'm not crazy about this solution because of the use of INDIRECT, the limitations of using CHAR to create the column letter, and casting a wide net in guessing where the spill(s) might be.
=LET( FindSpill, LAMBDA(a, v, LET( cel, CHAR(COLUMN(v) + 64) & ROW(v) & "#", IF(ISREF(INDIRECT(cel)), VSTACK(a, cel), a) ) ), REDUCE("Spill locations", arr, FindSpill) )Patrick2788 I admire the effort but that isn't a viable solution. I take the silence on the forum to mean that there is no standard facility to find to the TLC.
PS: your char+64 will fail starting from column AA 😉
Indeed, that's what I meant when I mentioned the limitations of CHAR.
If only AREAS was more robust . .
