"Currentregion" concept in Google Sheets script language

Question

From inside Google Sheets Script, I am trying to name a variable-sized matrix on the worksheet. In EXCEL VBA, I would go to the top left most cell and select the whole matrix using activecell.currentregion.select. This would select the whole matrix (e.g. D5:L50) on the worksheet, which I could then name.

Is there the same ability in Google Sheets script language. If not, can anyone figure out how to do this?

In Google Sheets, you can go to **Data-> Named Ranges** and use the UI to create a Named Range. Also you can use Apps Script for achieving this, [here is the official documentation](https://developers.google.com/apps-script/reference/spreadsheet/named-range). Do named ranges fit your situation ? Were they what you were trying to achieve ? If not, could you please clarify more your objectives ? Thanks ! — Mateo Randwolf, Apr 03 '20 at 07:35

score 1 · Accepted Answer · answered Apr 02 '20 at 15:59

1

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('D5:L50').activate();

you can use macro under:

Tools > Macros > Record macro > and then make your selection > save macro > edit macro

answered Apr 02 '20 at 15:59

player0

124,011
12
67
124

Thanks, you taught me something about getrange. However, the issue is I only know, in my example, cell D5. From that cell, I want to activate all cells contiguous to the right until a blank column and all rows down until a blank row. – Martin Feinstein Apr 02 '20 at 23:44

score 1 · Answer 2 · answered May 12 '21 at 11:37

SpreadsheetApp.Range

getDataRegion():

Returns a copy of the range expanded in the four cardinal Directions to cover all adjacent cells with data in them. If the range is surrounded by empty cells not including those along the diagonals, the range itself is returned. This is similar to selecting the range and typing Ctrl+A in the editor.

score 0 · Answer 3 · answered Apr 16 '20 at 07:36

The question is: how do you refer to a range which contains values and most importantly, the range may change as user add or remove contents. In Excel VBA, this is dealt with a built-in function called "currentregion". I'm also curious if there is a equivalent in GS.

"Currentregion" concept in Google Sheets script language

3 Answers3