I am trying to write a macro that resizes an array formula or in other words a formula that I can run when the number of items returned by the array formula changes, to avoid the ulgy “#N/A” cells at the end of the array.
I have found one example but it doesn’t seem to work as it requires a getCurrentArray
function which doesn’t seem to be defined, however the basic premise seems sound (i.e. delete the existing formula and re-insert a new one).
I have tried to just call the setArrayFormula
method on a single cell in the array, but unsurprisingly that doesn’t work (annoyingly, it just silently does nothing) because you need to be working with the whole array, similar to the way you can press CTRL+/ when doing it normally. If I expand the range to include the entire row (or a range which I know is larger than the array formula), then it all works as expected.
I tried recording a macro and pressing CTRL+/, and the result was:
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:SelectArrayFormula", "", 0, Array())
This sort of works, but:
- it’s a bit of a cludge
- you have to have the spreadsheet window focussed (not the basic window, annoying when developing)
- doesn’t quite do what I want, it does select the correct cells but doesn’t return them as a range, so then I’d have to go another step to find the current selection and get the range from that
So my question is if I have one cell which is part of an array formula how can I find the size/range for all the cells that are part of the array formula to enable me to edit/delete it.