Request for a Union method for the Excel office-js library

Request for a Union method for the Excel office-js library



 May 09 2022
3 Comments (3 New)

In the VBA Object library, the Application object  has a Union method. This method allows one to easily gather (non-)contiguous ranges into one collection. The Union method is smart enough to build contiguous ranges where possible (it creates one range A1:A10 if passed cells A1 to A10 in any order).


The office-js object library does not expose a Union method in the Excel context and it would be a welcome addition. For example when creating an application that searches for a string in all formulas of a sheet.


If we use the findAll method of the worksheet object, office-js reports all found cells individually, potentially resulting in many single cells.


Using the Union method one could build contiguous ranges of cells that share the same formulaR1C1. This way we can prevent reporting hundreds or even thousands of single cells to the user and summarize found items into their contiguous ranges containing a "copied" formula.


In VBA, Union is a very efficient way to first collect blocks of contiguous ranges prior to doing an operation on in one command, such as changing their formatting. I expect this may apply to office-js as well.

Silver Contributor


Copper Contributor

I'm looking for the same method. I opened an issue on GitHub and hope that someone from their team would read it.

Silver Contributor

 I've been working on my own version, which now works but is too slow to be practical. Happy to share if yo like to try. It needs rewriting so it uses less API calls and handles everything using arrays of indexes but I haven't had the time to do that yet.