CODEWITHSUNDEEP
exceloffice-js
Charles Williams
Charles Williams

Reputation: 23540

Get Current Region with Office-JS

How do I get the current region surrounding the ActiveCell using the Excel JS API?

In VBA this is

Set rng=ActiveCell.CurrentRegion

Upvotes: 3

Views: 1043

Answers (2)

beyphy
beyphy

Reputation: 321

The current region property in the JavaScript API has now been implemented. The property is called getSurroundingRegion()

Upvotes: 3

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

There is no direct equivalent, but we do have a range.getUsedRange() that will take an existing range and give you a smaller range that represents the non-empty portions. Note that this method will throw a not-found error if there is nothing in the entire range (since effectively it's an empty range, which Excel can't express).

If you really need the CurrentRegion scenario (and I'd be curious to learn more), you could first get the used range (to ensure you're not loading too much data), then load the values property, and then do range.getExpandedRange(indexOfLastRow, indexOfLastColumn).

BTW, unlike VBA's usedRange, the JS "getUsedRange()" always creates an accurate snapshot of the current used range (the VBA one could get stale), and we're exposing it not just on the worksheet but also on a given range.

Update

What I mean is that there are a couple of scenario, one simpler, the other harder.

The simpler one: you know roughly what range you need, but you just need to trim it. For example, you know you have a table-like entity in columns A:C, but you don't know the row count. That's where

worksheet.getRange("A:C").getUsedRange()

would get you what you need.

The harder one: you use getUsedRange() to trim down what you can, but you then load range.values and manually do a search for rows and columns where each cell is empty (""). Once you have that (suppose you found that the relative row index you care about is 5, and column index 2), you could do 

originalRange.getCell(0, 0).getExpandedRange(rowIndex, columnIndex)

Concrete example for the above: You have data in A2:C7, though the getUsedRange() of the worksheet is much larger (and hence my suggestion could try to trim it down further by doing a range.getUsedRange()). But for this case, let's imagine that getUsedRange on a worksheet returned a range corresponding to A1:Z100. worksheet.getRange(0, 0) would get you the first cell, which you can then expand by 5 rows and 2 columns (which you find through simple albeit tedious array iteration) to get the range you care about. Makes sense?

Upvotes: 1

Related Questions