Excel.Range class

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

readonly address: string;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

readonly addressLocal: string;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

Remarks

[ API set: ExcelApi 1.1 ]

Specifies the total number of columns in the range.

readonly columnCount: number;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

Remarks

[ API set: ExcelApi 1.2 ]

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

readonly columnIndex: number;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

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

readonly format: Excel.RangeFormat;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

formulas: any[][];

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

Remarks

[ API set: ExcelApi 1.1 ]

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

formulasR1C1: any[][];

Property Value

Remarks

[ API set: ExcelApi 1.2 ]

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

Remarks

[ API set: ExcelApi 1.2 ]

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

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);
});

Returns the total number of rows in the range.

readonly rowCount: number;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

Remarks

[ API set: ExcelApi 1.2 ]

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

readonly rowIndex: number;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

Represents the range sort of the current range.

readonly sort: Excel.RangeSort;

Property Value

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 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

Remarks

[ API set: ExcelApi 1.1 ]

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

Remarks

[ API set: ExcelApi 1.1 ]

Specifies the type of data in each cell.

readonly valueTypes: Excel.RangeValueType[][];

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

The worksheet containing the current range.

readonly worksheet: Excel.Worksheet;

Property Value

Remarks

[ API set: ExcelApi 1.1 ]

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

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

Parameters

Returns

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 range values and formatting, such as fill and border.

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.1 ]

Deletes the cells associated with the range.

delete(shift: Excel.DeleteShiftDirection): void;

Parameters

Returns

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(); 
});

Deletes the cells associated with the range.

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.1 ]

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

Returns

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
});

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

Returns

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);
});

Gets a column contained in the range.

getColumn(column: number): Excel.Range;

Parameters

Returns

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
});

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

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.2 ]

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

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.2 ]

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

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);
});

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

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);
});

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

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

Parameters

Returns

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
});

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

getLastCell(): Excel.Range;

Returns

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
});

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

getLastColumn(): Excel.Range;

Returns

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
});

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

getLastRow(): Excel.Range;

Returns

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
});

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

Returns

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
});

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

Returns

Remarks

[ API set: ExcelApi 1.2 ]

Gets a row contained in the range.

getRow(row: number): Excel.Range;

Parameters

Returns

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
});

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

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.2 ]

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

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

Parameters

Returns

Remarks

[ API set: ExcelApi 1.2 ]

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

Returns

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();
});

Represents the visible rows of the current range.

getVisibleView(): Excel.RangeView;

Returns

Remarks

[ API set: ExcelApi 1.3 ]

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

Returns

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();
});

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

Returns

Remarks

[ API set: ExcelApi 1.1 ]

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

Returns

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

Returns

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);
});

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

Returns

Merge the range cells into one region in the worksheet.

merge(across?: boolean): void;

Parameters

Returns

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();
});

Selects the specified range in the Excel UI.

select(): void;

Returns

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(); 
});

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

Returns

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

set(properties: Excel.Range): void;

Parameters

Returns

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();
});

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

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

Unmerge the range cells into separate cells.

unmerge(): void;

Returns

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(); 
});

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

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();
});