ExcelScript.RangeAreas interface
RangeAreas represents a collection of one or more rectangular ranges in the same worksheet.
Remarks
Examples
/**
* This script clears all the cells in the current worksheet that are displaying errors.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the used range on the current sheet.
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// Get the RangeAreas object for each cell with a formula error.
const errorCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas, ExcelScript.SpecialCellValueType.errors);
// If there are any error cells, clear them.
if (errorCells) {
errorCells.clear();
}
}
Methods
| add |
Adds a new conditional format to the collection at the first/top priority. |
| calculate() | Calculates all cells in the |
| clear(apply |
Clears values, format, fill, border, and other properties on each of the areas that comprise this |
| clear |
Clears all conditional formats active on the current specified range. |
| convert |
Converts all cells in the |
| copy |
Copies cell data or formatting from the source range or |
| get |
Returns the |
| get |
Returns the |
| get |
Returns the number of rectangular ranges that comprise this |
| get |
Returns a collection of rectangular ranges that comprise this |
| get |
Returns the number of cells in the |
| get |
Returns a conditional format identified by its ID. If the conditional format object does not exist, then this method returns |
| get |
Returns a collection of conditional formats that intersect with any cells in this |
| get |
Returns a data validation object for all ranges in the |
| get |
Returns a |
| get |
Returns a |
| get |
Returns a |
| get |
Returns the |
| get |
Specifies if all the ranges on this |
| get |
Specifies if all the ranges on this |
| get |
Returns a |
| get |
Represents the style for all ranges in this |
| get |
Returns a |
| get |
Returns a scoped collection of tables that overlap with any range in this |
| get |
Returns the used |
| get |
Returns the worksheet for the current |
| set |
Sets the |
| set |
Represents the style for all ranges in this |
Method Details
addConditionalFormat(type)
Adds a new conditional format to the collection at the first/top priority.
addConditionalFormat(type: ConditionalFormatType): ConditionalFormat;Parameters
The type of conditional format being added. See ExcelScript.ConditionalFormatType for details.
Returns
calculate()
Calculates all cells in the RangeAreas.
calculate(): void;Returns
void
clear(applyTo)
Clears values, format, fill, border, and other properties on each of the areas that comprise this RangeAreas object.
clear(applyTo?: ClearApplyTo): void;Parameters
- applyTo
- ExcelScript.ClearApplyTo
Optional. Determines the type of clear action. See ExcelScript.ClearApplyTo for details. Default is "All".
Returns
void
Examples
/**
* This script clears all the cells in the current worksheet that are displaying errors.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the used range on the current sheet.
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// Get the RangeAreas object for each cell with a formula error.
const errorCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas, ExcelScript.SpecialCellValueType.errors);
// If there are any error cells, clear them.
if (errorCells) {
errorCells.clear();
}
}
clearAllConditionalFormats()
Clears all conditional formats active on the current specified range.
clearAllConditionalFormats(): void;Returns
void
convertDataTypeToText()
Converts all cells in the RangeAreas with data types into text.
convertDataTypeToText(): void;Returns
void
copyFrom(sourceRange, copyType, skipBlanks, transpose)
Copies cell data or formatting from the source range or RangeAreas to the current RangeAreas. The destination RangeAreas can be a different size than the source range or RangeAreas. The destination will be expanded automatically if it is smaller than the source.
copyFrom(
sourceRange: Range | RangeAreas | string,
copyType?: RangeCopyType,
skipBlanks?: boolean,
transpose?: boolean
): void;Parameters
- sourceRange
-
ExcelScript.Range | ExcelScript.RangeAreas | string
The source range or RangeAreas to copy from. When the source RangeAreas has multiple ranges, their form must able to be created by removing full rows or columns from a rectangular range.
- copyType
- ExcelScript.RangeCopyType
The type of cell data or formatting to copy over. Default is "All".
- skipBlanks
-
boolean
True if to skip blank cells in the source range or RangeAreas. Default is false.
- transpose
-
boolean
True if to transpose the cells in the destination RangeAreas. Default is false.
Returns
void
getAddress()
Returns the RangeAreas reference in A1-style. Address value will contain the worksheet name for each rectangular block of cells (e.g., "Sheet1!A1:B4, Sheet1!D1:D4").
getAddress(): string;Returns
string
getAddressLocal()
Returns the RangeAreas reference in the user locale.
getAddressLocal(): string;Returns
string
getAreaCount()
Returns the number of rectangular ranges that comprise this RangeAreas object.
getAreaCount(): number;Returns
number
getAreas()
Returns a collection of rectangular ranges that comprise this RangeAreas object.
getAreas(): Range[];Returns
getCellCount()
Returns the number of cells in the RangeAreas object, summing up the cell counts of all of the individual rectangular ranges. Returns -1 if the cell count exceeds 2^31-1 (2,147,483,647).
getCellCount(): number;Returns
number
getConditionalFormat(id)
Returns a conditional format identified by its ID. If the conditional format object does not exist, then this method returns undefined.
getConditionalFormat(id: string): ConditionalFormat | undefined;Parameters
- id
-
string
The ID of the conditional format.
Returns
ExcelScript.ConditionalFormat | undefined
getConditionalFormats()
Returns a collection of conditional formats that intersect with any cells in this RangeAreas object.
getConditionalFormats(): ConditionalFormat[];Returns
getDataValidation()
Returns a data validation object for all ranges in the RangeAreas.
getDataValidation(): DataValidation;Returns
getEntireColumn()
Returns a RangeAreas object that represents the entire columns of the RangeAreas (for example, if the current RangeAreas represents cells "B4:E11, H2", it returns a RangeAreas that represents columns "B:E, H:H").
getEntireColumn(): RangeAreas;Returns
getEntireRow()
Returns a RangeAreas object that represents the entire rows of the RangeAreas (for example, if the current RangeAreas represents cells "B4:E11", it returns a RangeAreas that represents rows "4:11").
getEntireRow(): RangeAreas;Returns
getFormat()
Returns a RangeFormat object, encapsulating the font, fill, borders, alignment, and other properties for all ranges in the RangeAreas object.
getFormat(): RangeFormat;Returns
Examples
/**
* This script finds and highlights all the cells in the current worksheet that contain a formula.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the used range on the current sheet.
const currentSheet = workbook.getActiveWorksheet();
const usedRange = currentSheet.getUsedRange();
// Get the RangeAreas object for each cell with a formula.
const formulaCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);
// Add a light blue background to the cells containing formulas.
formulaCells.getFormat().getFill().setColor("#ADD8E6");
}
getIntersection(anotherRange)
Returns the RangeAreas object that represents the intersection of the given ranges or RangeAreas. If no intersection is found, then this method returns undefined.
getIntersection(anotherRange: Range | RangeAreas | string): RangeAreas;Parameters
- anotherRange
-
ExcelScript.Range | ExcelScript.RangeAreas | string
The range, RangeAreas object, or address that will be used to determine the intersection.
Returns
getIsEntireColumn()
Specifies if all the ranges on this RangeAreas object represent entire columns (e.g., "A:C, Q:Z").
getIsEntireColumn(): boolean;Returns
boolean
getIsEntireRow()
Specifies if all the ranges on this RangeAreas object represent entire rows (e.g., "1:3, 5:7").
getIsEntireRow(): boolean;Returns
boolean
getOffsetRangeAreas(rowOffset, columnOffset)
Returns a RangeAreas object that is shifted by the specific row and column offset. The dimension of the returned RangeAreas will match the original object. If the resulting RangeAreas is forced outside the bounds of the worksheet grid, an error will be thrown.
getOffsetRangeAreas(
rowOffset: number,
columnOffset: number
): RangeAreas;Parameters
- rowOffset
-
number
The number of rows (positive, negative, or 0) by which the RangeAreas 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 RangeAreas is to be offset. Positive values are offset to the right, and negative values are offset to the left.
Returns
getPredefinedCellStyle()
Represents the style for all ranges in this RangeAreas object. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.
getPredefinedCellStyle(): string;Returns
string
getSpecialCells(cellType, cellValueType)
Returns a RangeAreas object that represents all the cells that match the specified type and value. If no special cells are found that match the criteria, then this method returns undefined.
getSpecialCells(
cellType: SpecialCellType,
cellValueType?: SpecialCellValueType
): RangeAreas;Parameters
- cellType
- ExcelScript.SpecialCellType
The type of cells to include.
- cellValueType
- ExcelScript.SpecialCellValueType
If cellType is either constants or formulas, this argument is used to determine which types of cells to include in the result. These values can be combined together to return more than one type. The default is to select all constants or formulas, no matter what the type.
Returns
getTables(fullyContained)
Returns a scoped collection of tables that overlap with any range in this RangeAreas object.
getTables(fullyContained?: boolean): Table[];Parameters
- fullyContained
-
boolean
If true, returns only tables that are fully contained within the range bounds. Default is false.
Returns
getUsedRangeAreas(valuesOnly)
Returns the used RangeAreas that comprises all the used areas of individual rectangular ranges in the RangeAreas object. If there are no used cells within the RangeAreas, then this method returns undefined.
getUsedRangeAreas(valuesOnly?: boolean): RangeAreas;Parameters
- valuesOnly
-
boolean
Whether to only consider cells with values as used cells.
Returns
getWorksheet()
Returns the worksheet for the current RangeAreas.
getWorksheet(): Worksheet;Returns
setDirty()
Sets the RangeAreas to be recalculated when the next recalculation occurs.
setDirty(): void;Returns
void
setPredefinedCellStyle(predefinedCellStyle)
Represents the style for all ranges in this RangeAreas object. If the styles of the cells are inconsistent, null will be returned. For custom styles, the style name will be returned. For built-in styles, a string representing a value in the BuiltInStyle enum will be returned.
setPredefinedCellStyle(predefinedCellStyle: string): void;Parameters
- predefinedCellStyle
-
string
Returns
void
Examples
/**
* This script sets the cell style to "warning text"
* on every cell with a formula error.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the used range in the current worksheet.
const sheet = workbook.getActiveWorksheet();
const usedRange = sheet.getUsedRange();
// Get any cells that are displaying errors.
const errorCells = usedRange.getSpecialCells(
ExcelScript.SpecialCellType.formulas,
ExcelScript.SpecialCellValueType.errors
);
// Check if there are error cells before proceeding.
if (errorCells) {
// Use the built-in warning text style on the error cells.
errorCells.setPredefinedCellStyle(
ExcelScript.BuiltInStyle.warningText.toString()
);
} else {
console.log("No formula errors in the worksheet.");
}
}
Office Scripts
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for