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

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

Upvotes

Upvote

 May 09 2022
0 Comments 
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.