Excel.Range class

Reference

Package:: excel

Range represents a set of one or more contiguous cells such as a cell, a row, a column, or a block of cells. To learn more about how ranges are used throughout the API, start with Ranges in the Excel JavaScript API.

Extends: OfficeExtension.ClientObject

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Get a Range object by its address.
await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    const cell = range.getCell(0,0);
    cell.load('address');
    await context.sync();
    
    console.log(cell.address);
});

Properties

address	Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").
addressLocal	Represents the range reference for the specified range in the language of the user.
cellCount	Specifies the number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647).
columnCount	Specifies the total number of columns in the range.
columnHidden	Represents if all columns in the current range are hidden. Value is `true` when all columns in a range are hidden. Value is `false` when no columns in the range are hidden. Value is `null` when some columns in a range are hidden and other columns in the same range are not hidden.
columnIndex	Specifies the column number of the first cell in the range. Zero-indexed.
context	The request context associated with the object. This connects the add-in's process to the Office host application's process.
format	Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.
formulas	Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.
formulasLocal	Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. If a cell has no formula, its value is returned instead.
formulasR1C1	Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.
hidden	Represents if all cells in the current range are hidden. Value is `true` when all cells in a range are hidden. Value is `false` when no cells in the range are hidden. Value is `null` when some cells in a range are hidden and other cells in the same range are not hidden.
numberFormat	Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.
rowCount	Returns the total number of rows in the range.
rowHidden	Represents if all rows in the current range are hidden. Value is `true` when all rows in a range are hidden. Value is `false` when no rows in the range are hidden. Value is `null` when some rows in a range are hidden and other rows in the same range are not hidden.
rowIndex	Returns the row number of the first cell in the range. Zero-indexed.
sort	Represents the range sort of the current range.
text	Text values of the specified range. The text value will not depend on the cell width. The number sign (#) substitution that happens in the Excel UI will not affect the text value returned by the API.
values	Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.
valueTypes	Specifies the type of data in each cell.
worksheet	The worksheet containing the current range.

Methods

clear(applyTo)	Clear range values and formatting, such as fill and border.
clear(applyToString)	Clear range values and formatting, such as fill and border.
delete(shift)	Deletes the cells associated with the range.
delete(shiftString)	Deletes the cells associated with the range.
getBoundingRect(anotherRange)	Gets the smallest range object that encompasses the given ranges. For example, the `GetBoundingRect` of "B2:C5" and "D10:E15" is "B2:E15".
getCell(row, column)	Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.
getColumn(column)	Gets a column contained in the range.
getColumnsAfter(count)	Gets a certain number of columns to the right of the current `Range` object.
getColumnsBefore(count)	Gets a certain number of columns to the left of the current `Range` object.
getEntireColumn()	Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its `getEntireColumn` is a range that represents columns "B:E").
getEntireRow()	Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its `GetEntireRow` is a range that represents rows "4:11").
getIntersection(anotherRange)	Gets the range object that represents the rectangular intersection of the given ranges.
getLastCell()	Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".
getLastColumn()	Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".
getLastRow()	Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".
getOffsetRange(rowOffset, columnOffset)	Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.
getResizedRange(deltaRows, deltaColumns)	Gets a `Range` object similar to the current `Range` object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.
getRow(row)	Gets a row contained in the range.
getRowsAbove(count)	Gets a certain number of rows above the current `Range` object.
getRowsBelow(count)	Gets a certain number of rows below the current `Range` object.
getUsedRange(valuesOnly)	Returns the used range of the given range object. If there are no used cells within the range, this function will throw an `ItemNotFound` error.
insert(shift)	Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new `Range` object at the now blank space.
insert(shiftString)	Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new `Range` object at the now blank space.
load(options)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
load(propertyNames)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
load(propertyNamesAndPaths)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
merge(across)	Merge the range cells into one region in the worksheet.
select()	Selects the specified range in the Excel UI.
set(properties, options)	Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.
set(properties)	Sets multiple properties on the object at the same time, based on an existing loaded object.
toJSON()	Overrides the JavaScript `toJSON()` method in order to provide more useful output when an API object is passed to `JSON.stringify()`. (`JSON.stringify`, in turn, calls the `toJSON` method of the object that is passed to it.) Whereas the original `Excel.Range` object is an API object, the `toJSON` method returns a plain JavaScript object (typed as `Excel.Interfaces.RangeData`) that contains shallow copies of any loaded child properties from the original object.
track()	Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you're using this object across `.sync` calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you need to add the object to the tracked object collection when the object was first created.
unmerge()	Unmerge the range cells into separate cells.
untrack()	Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call `context.sync()` before the memory release takes effect.

Property Details

address

Specifies the range reference in A1-style. Address value contains the sheet reference (e.g., "Sheet1!A1:B4").

readonly address: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

addressLocal

Represents the range reference for the specified range in the language of the user.

readonly addressLocal: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.1 ]

cellCount

Specifies the number of cells in the range. This API will return -1 if the cell count exceeds 2^31-1 (2,147,483,647).

readonly cellCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

columnCount

Specifies the total number of columns in the range.

readonly columnCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

columnHidden

Represents if all columns in the current range are hidden. Value is true when all columns in a range are hidden. Value is false when no columns in the range are hidden. Value is null when some columns in a range are hidden and other columns in the same range are not hidden.

columnHidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

columnIndex

Specifies the column number of the first cell in the range. Zero-indexed.

readonly columnIndex: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

Excel.RequestContext

format

Returns a format object, encapsulating the range's font, fill, borders, alignment, and other properties.

readonly format: Excel.RangeFormat;

Property Value

Excel.RangeFormat

Remarks

[ API set: ExcelApi 1.1 ]

formulas

Represents the formula in A1-style notation. If a cell has no formula, its value is returned instead.

formulas: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

formulasLocal

Represents the formula in A1-style notation, in the user's language and number-formatting locale. For example, the English "=SUM(A1, 1.5)" formula would become "=SUMME(A1; 1,5)" in German. If a cell has no formula, its value is returned instead.

formulasLocal: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

formulasR1C1

Represents the formula in R1C1-style notation. If a cell has no formula, its value is returned instead.

formulasR1C1: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.2 ]

hidden

Represents if all cells in the current range are hidden. Value is true when all cells in a range are hidden. Value is false when no cells in the range are hidden. Value is null when some cells in a range are hidden and other cells in the same range are not hidden.

readonly hidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

numberFormat

Represents Excel's number format code for the given range. For more information about Excel number formatting, see Number format codes.

numberFormat: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Set the text of the chart title to "My Chart" and display it as an overlay on the chart.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "F5:G7";
    const numberFormat = [[null, "d-mmm"], [null, "d-mmm"], [null, null]]
    const values = [["Today", 42147], ["Tomorrow", "5/24"], ["Difference in days", null]];
    const formulas = [[null,null], [null,null], [null,"=G6-G5"]];
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.numberFormat = numberFormat;
    range.values = values;
    range.formulas= formulas;
    range.load('text');
    await context.sync();
    
    console.log(range.text);
});

rowCount

Returns the total number of rows in the range.

readonly rowCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

rowHidden

Represents if all rows in the current range are hidden. Value is true when all rows in a range are hidden. Value is false when no rows in the range are hidden. Value is null when some rows in a range are hidden and other rows in the same range are not hidden.

rowHidden: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.2 ]

rowIndex

Returns the row number of the first cell in the range. Zero-indexed.

readonly rowIndex: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.1 ]

sort

Represents the range sort of the current range.

readonly sort: Excel.RangeSort;

Property Value

Excel.RangeSort

Remarks

[ API set: ExcelApi 1.2 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/event-column-and-row-sort.yaml

async function sortTopToBottom(criteria: string) {
    await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        const range = sheet.getRange("A1:E5");

        // Find the column header that provides the sort criteria.
        const header = range.find(criteria, {});
        header.load("columnIndex");
        await context.sync();

        range.sort.apply(
            [
                {
                    key: header.columnIndex,
                    sortOn: Excel.SortOn.value
                }
            ],
            false /*matchCase*/,
            true /*hasHeaders*/,
            Excel.SortOrientation.rows
        );
        await context.sync();
    });
}

text

Text values of the specified range. The text value will not depend on the cell width. The number sign (#) substitution that happens in the Excel UI will not affect the text value returned by the API.

readonly text: string[][];

Property Value

string[][]

Remarks

[ API set: ExcelApi 1.1 ]

values

Represents the raw values of the specified range. The data returned could be a string, number, or boolean. Cells that contain an error will return the error string. If the returned value starts with a plus ("+"), minus ("-"), or equal sign ("="), Excel interprets this value as a formula.

values: any[][];

Property Value

any[][]

Remarks

[ API set: ExcelApi 1.1 ]

valueTypes

Specifies the type of data in each cell.

readonly valueTypes: Excel.RangeValueType[][];

Property Value

Excel.RangeValueType[][]

Remarks

[ API set: ExcelApi 1.1 ]

worksheet

The worksheet containing the current range.

readonly worksheet: Excel.Worksheet;

Property Value

Excel.Worksheet

Remarks

[ API set: ExcelApi 1.1 ]

Method Details

clear(applyTo)

Clear range values and formatting, such as fill and border.

clear(applyTo?: Excel.ClearApplyTo): void;

Parameters

applyTo: Excel.ClearApplyTo

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Clear the format and contents of the range.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.clear();
    await context.sync(); 
});

clear(applyToString)

Clear range values and formatting, such as fill and border.

clear(applyToString?: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"): void;

Parameters

applyToString: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks"

Optional. Determines the type of clear action. See Excel.ClearApplyTo for details.

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

delete(shift)

Deletes the cells associated with the range.

delete(shift: Excel.DeleteShiftDirection): void;

Parameters

shift: Excel.DeleteShiftDirection

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.delete("Left");
    await context.sync(); 
});

delete(shiftString)

Deletes the cells associated with the range.

delete(shiftString: "Up" | "Left"): void;

Parameters

shiftString: "Up" | "Left"

Specifies which way to shift the cells. See Excel.DeleteShiftDirection for details.

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

getBoundingRect(anotherRange)

Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of "B2:C5" and "D10:E15" is "B2:E15".

getBoundingRect(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange: Excel.Range | string

The range object, address, or range name.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D4:G6";
    let range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range = range.getBoundingRect("G4:H8");
    range.load('address');
    await context.sync();
    
    console.log(range.address); // Prints Sheet1!D4:H8
});

getCell(row, column)

Gets the range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid. The returned cell is located relative to the top left cell of the range.

getCell(row: number, column: number): Excel.Range;

Parameters

row: number

Row number of the cell to be retrieved. Zero-indexed.

column: number

Column number of the cell to be retrieved. Zero-indexed.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    const cell = range.getCell(0,0);
    cell.load('address');
    await context.sync();
    
    console.log(cell.address);
});

getColumn(column)

Gets a column contained in the range.

getColumn(column: number): Excel.Range;

Parameters

column: number

Column number of the range to be retrieved. Zero-indexed.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet19";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getColumn(1);
    range.load('address');
    await context.sync();

    console.log(range.address); // prints Sheet1!B1:B8
});

getColumnsAfter(count)

Gets a certain number of columns to the right of the current Range object.

getColumnsAfter(count?: number): Excel.Range;

Parameters

count: number

Optional. The number of columns to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.2 ]

getColumnsBefore(count)

Gets a certain number of columns to the left of the current Range object.

getColumnsBefore(count?: number): Excel.Range;

Parameters

count: number

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.2 ]

getEntireColumn()

Gets an object that represents the entire column of the range (for example, if the current range represents cells "B4:E11", its getEntireColumn is a range that represents columns "B:E").

getEntireColumn(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Note: the grid properties of the Range (values, numberFormat, formulas) 
// contains null since the Range in question is unbounded.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D:F";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    const rangeEC = range.getEntireColumn();
    rangeEC.load('address');
    await context.sync();
    
    console.log(rangeEC.address);
});

getEntireRow()

Gets an object that represents the entire row of the range (for example, if the current range represents cells "B4:E11", its GetEntireRow is a range that represents rows "4:11").

getEntireRow(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Gets an object that represents the entire row of the range 
// (for example, if the current range represents cells "B4:E11", 
// its GetEntireRow is a range that represents rows "4:11").
await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "D:F"; 
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    const rangeER = range.getEntireRow();
    rangeER.load('address');
    await context.sync();
    
    console.log(rangeER.address);
});

getIntersection(anotherRange)

Gets the range object that represents the rectangular intersection of the given ranges.

getIntersection(anotherRange: Range | string): Excel.Range;

Parameters

anotherRange: Excel.Range | string

The range object or range address that will be used to determine the intersection of ranges.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = 
        context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getIntersection("D4:G6");
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!D4:F6
});

getLastCell()

Gets the last cell within the range. For example, the last cell of "B2:D5" is "D5".

getLastCell(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastCell();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!F8
});

getLastColumn()

Gets the last column within the range. For example, the last column of "B2:D5" is "D2:D5".

getLastColumn(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastColumn();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!F1:F8
});

getLastRow()

Gets the last row within the range. For example, the last row of "B2:D5" is "B5:D5".

getLastRow(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getLastRow();
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!A8:F8
});

getOffsetRange(rowOffset, columnOffset)

Gets an object which represents a range that's offset from the specified range. The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an error will be thrown.

getOffsetRange(rowOffset: number, columnOffset: number): Excel.Range;

Parameters

rowOffset: number

The number of rows (positive, negative, or 0) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward.

columnOffset: number

The number of columns (positive, negative, or 0) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "D4:F6";
    const range = 
        context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getOffsetRange(-1,4);
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!H3:J5
});

getResizedRange(deltaRows, deltaColumns)

Gets a Range object similar to the current Range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

getResizedRange(deltaRows: number, deltaColumns: number): Excel.Range;

Parameters

deltaRows: number

The number of rows by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

deltaColumns: number

The number of columns by which to expand the bottom-right corner, relative to the current range. Use a positive number to expand the range, or a negative number to decrease it.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.2 ]

getRow(row)

Gets a row contained in the range.

getRow(row: number): Excel.Range;

Parameters

row: number

Row number of the range to be retrieved. Zero-indexed.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(1);
    range.load('address');
    await context.sync();
    
    console.log(range.address); // prints Sheet1!A2:F2
});

getRowsAbove(count)

Gets a certain number of rows above the current Range object.

getRowsAbove(count?: number): Excel.Range;

Parameters

count: number

Optional. The number of rows to include in the resulting range. In general, use a positive number to create a range outside the current range. You can also use a negative number to create a range within the current range. The default value is 1.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.2 ]

getRowsBelow(count)

Gets a certain number of rows below the current Range object.

getRowsBelow(count?: number): Excel.Range;

Parameters

count: number

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.2 ]

getUsedRange(valuesOnly)

Returns the used range of the given range object. If there are no used cells within the range, this function will throw an ItemNotFound error.

getUsedRange(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly: boolean

Considers only cells with values as used cells. [Api set: ExcelApi 1.2]

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-relationships.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const salesTable = sheet.tables.getItem("SalesTable");
    const dataRange = salesTable.getDataBodyRange();

    // We want the most recent quarter that has data, so
    // exclude quarters without data and get the last of
    // the remaining columns.
    const usedDataRange = dataRange.getUsedRange(true /* valuesOnly */);
    const currentQuarterRange = usedDataRange.getLastColumn();

    // Asian and European teams have separate contests.
    const asianSalesRange = sheet.getRange("A2:E4");
    const europeanSalesRange = sheet.getRange("A5:E7");

    // The data for each chart is the intersection of the
    // current quarter column and the rows for the continent.
    const asianContestRange = asianSalesRange.getIntersectionOrNullObject(currentQuarterRange);
    const europeanContestRange = europeanSalesRange.getIntersectionOrNullObject(currentQuarterRange);

    // Must sync before you can test the output of *OrNullObject
    // method/property.
    await context.sync();

    if (asianContestRange.isNullObject) {
        // See the declaration of this function for how to
        // test this code path.
        reportMissingData("Asian");
    } else {
        createContinentChart(
            sheet,
            "Asian",
            asianContestRange,
            "A9",
            "F24"
        );
    }

    if (europeanContestRange.isNullObject) {
        // See the declaration of this function for how to
        // test this code path.
        reportMissingData("European");
    } else {
        createContinentChart(
            sheet,
            "European",
            europeanContestRange,
            "A25",
            "F40"
        );
    }

    await context.sync();
});

insert(shift)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

insert(shift: Excel.InsertShiftDirection): Excel.Range;

Parameters

shift: Excel.InsertShiftDirection

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "F5:F10";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.insert(Excel.InsertShiftDirection.down);
    await context.sync();
});

insert(shiftString)

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space. Returns a new Range object at the now blank space.

insert(shiftString: "Down" | "Right"): Excel.Range;

Parameters

shiftString: "Down" | "Right"

Specifies which way to shift the cells. See Excel.InsertShiftDirection for details.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.1 ]

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.RangeLoadOptions): Excel.Range;

Parameters

options: Excel.Interfaces.RangeLoadOptions

Provides options for which properties of the object to load.

Returns

Excel.Range

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.Range;

Parameters

propertyNames: string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Excel.Range

Examples

// Use the range address to get the range object.
await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8"; 
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    await context.sync();
    
    console.log(range.cellCount);
});

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.Range;

Parameters

propertyNamesAndPaths: { select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

Excel.Range

merge(across)

Merge the range cells into one region in the worksheet.

merge(across?: boolean): void;

Parameters

across: boolean

Optional. Set true to merge cells in each row of the specified range as separate merged cells. The default value is false.

Returns

void

Remarks

[ API set: ExcelApi 1.2 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:C3";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.merge(true);
    await context.sync(); 
});

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-merged-ranges.yaml

await Excel.run(async (context) => {
  // Retrieve the worksheet and the table in that worksheet.
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const tableRange = sheet.getRange("B2:E6");

  // Create a merged range in the first row of the table.
  const chartTitle = tableRange.getRow(0);
  chartTitle.merge(true);

  // Format the merged range.
  chartTitle.format.horizontalAlignment = "Center";

  await context.sync();
});

select()

Selects the specified range in the Excel UI.

select(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.1 ]

Examples

await Excel.run(async (context) => {
    const sheetName = "Sheet1";
    const rangeAddress = "F5:F10"; 
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.select();
    await context.sync(); 
});

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.RangeUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties: Excel.Interfaces.RangeUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options: OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Range): void;

Parameters

properties: Excel.Range

Returns

void

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/90-scenarios/multiple-property-set.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const sourceRange = sheet.getRange("B2:E2");
    sourceRange.load("format/fill/color, format/font/name, format/font/color");
    await context.sync();

    // Set properties based on the loaded and synced 
    // source range.
    const targetRange = sheet.getRange("B7:E7");
    targetRange.set(sourceRange); 
    targetRange.format.autofitColumns();
    await context.sync();
});

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Range object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.RangeData;

Returns

Excel.Interfaces.RangeData

track()

Track the object for automatic adjustment based on surrounding changes in the document. This call is a shorthand for context.trackedObjects.add(thisObject). If you're using this object across .sync calls and outside the sequential execution of a ".run" batch, and get an "InvalidObjectPath" error when setting a property or invoking a method on the object, you need to add the object to the tracked object collection when the object was first created.

track(): Excel.Range;

Returns

Excel.Range

unmerge()

Unmerge the range cells into separate cells.

unmerge(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.2 ]

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:C3";
    const range = context.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    range.unmerge();
    await context.sync(); 
});

untrack()

Release the memory associated with this object, if it has previously been tracked. This call is shorthand for context.trackedObjects.remove(thisObject). Having many tracked objects slows down the host application, so please remember to free any objects you add, once you're done using them. You will need to call context.sync() before the memory release takes effect.

untrack(): Excel.Range;

Returns

Excel.Range

Examples

await Excel.run(async (context) => {
    const largeRange = context.workbook.getSelectedRange();
    largeRange.load(["rowCount", "columnCount"]);
    await context.sync();

    for (let i = 0; i < largeRange.rowCount; i++) {
        for (let j = 0; j < largeRange.columnCount; j++) {
            const cell = largeRange.getCell(i, j);
            cell.values = [[i *j]];

            // Call untrack() to release the range from memory.
            cell.untrack();
        }
    }

    await context.sync();
});

Share via

Excel.Range class

Remarks

Examples

Properties

Methods

Property Details

address

Property Value

Remarks

addressLocal

Property Value

Remarks

cellCount

Property Value

Remarks

columnCount

Property Value

Remarks

columnHidden

Property Value

Remarks

columnIndex

Property Value

Remarks

context

Property Value

format

Property Value

Remarks

formulas

Property Value

Remarks

formulasLocal

Property Value

Remarks

formulasR1C1

Property Value

Remarks

hidden

Property Value

Remarks

numberFormat

Property Value

Remarks

Examples

rowCount

Property Value

Remarks

rowHidden

Property Value

Remarks

rowIndex

Property Value

Remarks

sort

Property Value

Remarks

Examples

text

Property Value

Remarks

values

Property Value

Remarks

valueTypes

Property Value

Remarks

worksheet

Property Value

Remarks

Method Details

clear(applyTo)

Parameters

Returns

Remarks

Examples

clear(applyToString)

Parameters

Returns