Edit

Share via

Set and get the selected range using the Excel JavaScript API

  • Article

This article provides code samples that set and get the selected range with the Excel JavaScript API. For the complete list of properties and methods that the Range object supports, see Excel.Range class.

Note

The Excel JavaScript API doesn't have a "Cell" object or class. Instead, the Excel JavaScript API defines all Excel cells as Range objects. An individual cell in the Excel UI translates to a Range object with one cell in the Excel JavaScript API. A single Range object can also contain multiple contiguous cells. See Work with cells using the Excel JavaScript API to learn more.

Set the selected range

The following code sample selects the range B2:E6 in the active worksheet.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    let range = sheet.getRange("B2:E6");

    range.select();

    await context.sync();
});

Selected range B2:E6

Selected range in Excel.

Get the selected range

The following code sample gets the selected range, loads its address property, and writes a message to the console.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    range.load("address");

    await context.sync();
    
    console.log(`The address of the selected range is "${range.address}"`);
});

Select the edge of a used range

The Range.getRangeEdge and Range.getExtendedRange methods let your add-in replicate the behavior of the keyboard selection shortcuts, selecting the edge of the used range based on the currently selected range. To learn more about used ranges, see Get used range.

In the following screenshot, the used range is the table with values in each cell, C5:F12. The empty cells outside this table are outside the used range.

A table with data from C5:F12 in Excel.

Select the cell at the edge of the current used range

The following code sample shows how use the Range.getRangeEdge method to select the cell at the furthest edge of the current used range, in the direction up. This action matches the result of using the Ctrl+Up arrow key keyboard shortcut while a range is selected.

await Excel.run(async (context) => {
    // Get the selected range.
    let range = context.workbook.getSelectedRange();

    // Specify the direction with the `KeyboardDirection` enum.
    let direction = Excel.KeyboardDirection.up;

    // Get the active cell in the workbook.
    let activeCell = context.workbook.getActiveCell();

    // Get the top-most cell of the current used range.
    // This method acts like the Ctrl+Up arrow key keyboard shortcut while a range is selected.
    let rangeEdge = range.getRangeEdge(
      direction,
      activeCell
    );
    rangeEdge.select();

    await context.sync();
});

Before selecting the cell at the edge of the used range

The following screenshot shows a used range and a selected range within the used range. The used range is a table with data at C5:F12. Inside this table, the range D8:E9 is selected. This selection is the before state, prior to running the Range.getRangeEdge method.

A table with data from C5:F12 in Excel. The range D8:E9 is selected.

After selecting the cell at the edge of the used range

The following screenshot shows the same table as the preceding screenshot, with data in the range C5:F12. Inside this table, the range D5 is selected. This selection is after state, after running the Range.getRangeEdge method to select the cell at the edge of the used range in the up direction.

A table with data from C5:F12 in Excel. The range D5 is selected.

Select all cells from current range to furthest edge of used range

The following code sample shows how use the Range.getExtendedRange method to to select all the cells from the currently selected range to the furthest edge of the used range, in the direction down. This action matches the result of using the Ctrl+Shift+Down arrow key keyboard shortcut while a range is selected.

await Excel.run(async (context) => {
    // Get the selected range.
    let range = context.workbook.getSelectedRange();

    // Specify the direction with the `KeyboardDirection` enum.
    let direction = Excel.KeyboardDirection.down;

    // Get the active cell in the workbook.
    let activeCell = context.workbook.getActiveCell();

    // Get all the cells from the currently selected range to the bottom-most edge of the used range.
    // This method acts like the Ctrl+Shift+Down arrow key keyboard shortcut while a range is selected.
    let extendedRange = range.getExtendedRange(
      direction,
      activeCell
    );
    extendedRange.select();

    await context.sync();
});

Before selecting all the cells from the current range to the edge of the used range

The following screenshot shows a used range and a selected range within the used range. The used range is a table with data at C5:F12. Inside this table, the range D8:E9 is selected. This selection is the before state, prior to running the Range.getExtendedRange method.

A table with data from C5:F12 in Excel. The range D8:E9 is selected.

After selecting all the cells from the current range to the edge of the used range

The following screenshot shows the same table as the preceding screenshot, with data in the range C5:F12. Inside this table, the range D8:E12 is selected. This selection is after state, after running the Range.getExtendedRange method to select all the cells from the current range to the edge of the used range in the down direction.

A table with data from C5:F12 in Excel. The range D8:E12 is selected.

See also