3

How do I select ranges by typing in the range value in Google Spreadsheets?

I need to be able to select, say, d3:d1000, by typing in “d3:d1000” somewhere. In other words, what F5 does in Excel. I have discovered named ranges; however, it is very cumbersome to create a single-use named range every time I want to select a one-off range. In Excel, I hit F5, type the range value, hit return, and it’s selected. What’s Google Spreadsheets’ equivalent?

Ranges can be entered fine in various places, including as arguments to functions such as SUM, or when applying conditional formatting via the modal dialog, so it seems very strange not to be able to enter them to perform simple selection.

1

You would need a google apps script for that. Something like this should work:

function onOpen() {
SpreadsheetApp.getUi() 
  .createMenu('Custom Menu')
  .addItem('Select Range', 'selectRange')
  .addToUi();
  }


function selectRange() {
var ui = SpreadsheetApp.getUi();
var sheet = SpreadsheetApp.getActiveSheet();
var response = ui.prompt('Enter the range:');
if (response.getSelectedButton() == ui.Button.OK) {
    sheet.setActiveRange(sheet.getRange(response.getResponseText()));
} else {
    ui.alert('Could not set an active range');
}
}

After pasting this in the script editor, run the onOpen function (via the 'play' button in the scripteditor). That should create an extra menu-item in your spreadsheet. Go back in your spreadsheet, find the menu-item 'Custom Menu' and click on 'Select Range'...

  • OK, but is there any conceivable reason Google does not build in this commonly needed functionality? – Alan Jul 5 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.