3

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

In VBA this is

Set rng=ActiveCell.CurrentRegion
Charles Williams
  • 23,121
  • 5
  • 38
  • 38

2 Answers2

3

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

beyphy
  • 311
  • 2
  • 7
1

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?

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • So if the sheet has multiple areas of data each of which is bounded by empty cells (think multiple tables which are not actually tables) I don't see how to make this work: could you explain more? – Charles Williams Oct 21 '16 at 18:29
  • And what do you mean by JS UsedRange is always accurate but VBA UsedRange can get stale - I definitely would not want a JS range I created from the usedrange to be automatically updated every time the used range changed. – Charles Williams Oct 21 '16 at 18:35
  • @CharlesWilliams, for your second comment: I edited my answer a bit. What I mean is that the JS "getUsedRange()" always creates an accurate snapshot of the current used range, whereas I have seen some instances where the Excel VBA one would report a used range that's out of date. – Michael Zlatkovsky - Microsoft Oct 21 '16 at 18:50
  • Thanks I can see how that works if the used range only has one area - but I still don't see how to handle the situation where the used range contains multiple areas (think tables) and I want to identify the currentregion of the one where the active cell is located. I suppose I could iterate left and right on every row and up and down on every column but thats mind-blowingly tedious and expensive. – Charles Williams Oct 21 '16 at 19:52
  • usedrange - Thanks for the explain - yes the VBA usedrange does not update correctly if you delete a large number of cells on a large complex worksheet in 2007 or later. Have not tested the JS API in a similar scenario - I assumed (maybe incorrectly) it was using the same internal Excel code to inspect the cell table. – Charles Williams Oct 21 '16 at 20:00
  • The usedrange algorithm we use is brand new, not VBA-related. So it does get updated correctly any time you invoke it. – Michael Zlatkovsky - Microsoft Oct 24 '16 at 17:54
  • I can think of an iterative algorithm which starts out with the current cell and -- having loaded the used range of the entire worksheet, keeps trying to find the upper left and bottom right quadrants, such that the range from topl-left to bottom right is fully surrounded by empty columns (or no rows/columns at all, for row/column-index 0). But yeah, it's not trivial, nor particularly performant. Let me add this to our backlog, to do at the same time as we add "getSelectedTable" (see that workaround at http://stackoverflow.com/questions/39729658/find-table-intersect-with-selection) – Michael Zlatkovsky - Microsoft Oct 24 '16 at 18:03
  • Thanks Michael - understood – Charles Williams Oct 25 '16 at 13:51