Excel.RangeAreas class
RangeAreas represents a collection of one or more rectangular ranges in the same worksheet. To learn how to use discontiguous ranges, read Work with multiple ranges simultaneously in Excel add-ins.
- Extends
Remarks
Properties
| address | Returns the |
| address |
Returns the |
| area |
Returns the number of rectangular ranges that comprise this |
| areas | Returns a collection of rectangular ranges that comprise this |
| cell |
Returns the number of cells in the |
| conditional |
Returns a collection of conditional formats that intersect with any cells in this |
| context | The request context associated with the object. This connects the add-in's process to the Office host application's process. |
| data |
Returns a data validation object for all ranges in the |
| format | Returns a |
| is |
Specifies if all the ranges on this |
| is |
Specifies if all the ranges on this |
| style | Represents the style for all ranges in this |
| worksheet | Returns the worksheet for the current |
Methods
| 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(apply |
Clears values, format, fill, border, and other properties on each of the areas that comprise this |
| clear |
Clears the values of the cells in the ranges, with special consideration given to cells containing controls. If the ranges contain only blank values and controls set to their default value, then the values and control formatting are removed. Otherwise, this sets the cells with controls to their default value and clears the values of the other cells in the ranges. |
| convert |
Converts all cells in the |
| convert |
Converts all cells in the |
| copy |
Copies cell data or formatting from the source range or |
| copy |
Copies cell data or formatting from the source range or |
| get |
Returns a |
| get |
Returns a |
| get |
Returns the |
| get |
Returns the |
| get |
Returns a |
| get |
Returns a |
| get |
Returns a |
| get |
Returns a |
| get |
Returns a |
| get |
Returns a scoped collection of tables that overlap with any range in this |
| get |
Returns the used |
| get |
Returns the used |
| load(options) | Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| load(property |
Queues up a command to load the specified properties of the object. You must call |
| 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. |
| set |
Sets the |
| toJSON() | Overrides the JavaScript |
| 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 |
| 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 |
Property Details
address
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").
readonly address: string;Property Value
string
Remarks
addressLocal
Returns the RangeAreas reference in the user locale.
readonly addressLocal: string;Property Value
string
Remarks
areaCount
Returns the number of rectangular ranges that comprise this RangeAreas object.
readonly areaCount: number;Property Value
number
Remarks
areas
Returns a collection of rectangular ranges that comprise this RangeAreas object.
readonly areas: Excel.RangeCollection;Property Value
Remarks
cellCount
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).
readonly cellCount: number;Property Value
number
Remarks
conditionalFormats
Returns a collection of conditional formats that intersect with any cells in this RangeAreas object.
readonly conditionalFormats: Excel.ConditionalFormatCollection;Property Value
Remarks
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
dataValidation
Returns a data validation object for all ranges in the RangeAreas.
readonly dataValidation: Excel.DataValidation;Property Value
Remarks
format
Returns a RangeFormat object, encapsulating the font, fill, borders, alignment, and other properties for all ranges in the RangeAreas object.
readonly format: Excel.RangeFormat;Property Value
Remarks
Examples
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-areas.yaml
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const usedRange = sheet.getUsedRange();
// Find the ranges with formulas.
const formulaRanges = usedRange.getSpecialCells("Formulas");
formulaRanges.format.fill.color = "lightgreen";
await context.sync();
});
isEntireColumn
Specifies if all the ranges on this RangeAreas object represent entire columns (e.g., "A:C, Q:Z").
readonly isEntireColumn: boolean;Property Value
boolean
Remarks
isEntireRow
Specifies if all the ranges on this RangeAreas object represent entire rows (e.g., "1:3, 5:7").
readonly isEntireRow: boolean;Property Value
boolean
Remarks
style
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.
style: string;Property Value
string
Remarks
worksheet
Returns the worksheet for the current RangeAreas.
readonly worksheet: Excel.Worksheet;Property Value
Remarks
Method Details
calculate()
Calculates all cells in the RangeAreas.
calculate(): void;Returns
void
Remarks
clear(applyTo)
Clears values, format, fill, border, and other properties on each of the areas that comprise this RangeAreas object.
clear(applyTo?: Excel.ClearApplyTo): void;Parameters
- applyTo
- Excel.ClearApplyTo
Optional. Determines the type of clear action. See Excel.ClearApplyTo for details. Default is "All".
Returns
void
Remarks
clear(applyToString)
Clears values, format, fill, border, and other properties on each of the areas that comprise this RangeAreas object.
clear(applyToString?: "All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks" | "ResetContents"): void;Parameters
- applyToString
-
"All" | "Formats" | "Contents" | "Hyperlinks" | "RemoveHyperlinks" | "ResetContents"
Optional. Determines the type of clear action. See Excel.ClearApplyTo for details. Default is "All".
Returns
void
Remarks
clearOrResetContents()
Note
This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.
Clears the values of the cells in the ranges, with special consideration given to cells containing controls. If the ranges contain only blank values and controls set to their default value, then the values and control formatting are removed. Otherwise, this sets the cells with controls to their default value and clears the values of the other cells in the ranges.
clearOrResetContents(): void;Returns
void
Remarks
convertDataTypeToText()
Converts all cells in the RangeAreas with data types into text.
convertDataTypeToText(): void;Returns
void
Remarks
convertToLinkedDataType(serviceID, languageCulture)
Converts all cells in the RangeAreas into linked data types.
convertToLinkedDataType(serviceID: number, languageCulture: string): void;Parameters
- serviceID
-
number
The service ID which will be used to query the data.
- languageCulture
-
string
Language culture to query the service for.
Returns
void
Remarks
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?: Excel.RangeCopyType, skipBlanks?: boolean, transpose?: boolean): void;Parameters
- sourceRange
-
Excel.Range | Excel.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
- Excel.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
Remarks
copyFrom(sourceRange, copyTypeString, 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, copyTypeString?: "All" | "Formulas" | "Values" | "Formats" | "Link" | "ColumnWidths", skipBlanks?: boolean, transpose?: boolean): void;Parameters
- sourceRange
-
Excel.Range | Excel.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.
- copyTypeString
-
"All" | "Formulas" | "Values" | "Formats" | "Link" | "ColumnWidths"
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
Remarks
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(): Excel.RangeAreas;Returns
Remarks
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(): Excel.RangeAreas;Returns
Remarks
getIntersection(anotherRange)
Returns the RangeAreas object that represents the intersection of the given ranges or RangeAreas. If no intersection is found, an ItemNotFound error will be thrown.
getIntersection(anotherRange: Range | RangeAreas | string): Excel.RangeAreas;Parameters
- anotherRange
-
Excel.Range | Excel.RangeAreas | string
The range, RangeAreas object, or range address that will be used to determine the intersection.
Returns
Remarks
getIntersectionOrNullObject(anotherRange)
Returns the RangeAreas object that represents the intersection of the given ranges or RangeAreas. If no intersection is found, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getIntersectionOrNullObject(anotherRange: Range | RangeAreas | string): Excel.RangeAreas;Parameters
- anotherRange
-
Excel.Range | Excel.RangeAreas | string
The range, RangeAreas object, or address that will be used to determine the intersection.
Returns
Remarks
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): Excel.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
Remarks
getSpecialCells(cellType, cellValueType)
Returns a RangeAreas object that represents all the cells that match the specified type and value. Throws an error if no special cells are found that match the criteria.
getSpecialCells(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;Parameters
- cellType
- Excel.SpecialCellType
The type of cells to include.
- cellValueType
- Excel.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
Remarks
getSpecialCells(cellTypeString, cellValueTypeString)
Returns a RangeAreas object that represents all the cells that match the specified type and value. Throws an error if no special cells are found that match the criteria.
getSpecialCells(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueTypeString?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;Parameters
- cellTypeString
-
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"
The type of cells to include.
- cellValueTypeString
-
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"
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
Remarks
getSpecialCellsOrNullObject(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 an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getSpecialCellsOrNullObject(cellType: Excel.SpecialCellType, cellValueType?: Excel.SpecialCellValueType): Excel.RangeAreas;Parameters
- cellType
- Excel.SpecialCellType
The type of cells to include.
- cellValueType
- Excel.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
Remarks
getSpecialCellsOrNullObject(cellTypeString, cellValueTypeString)
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 an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getSpecialCellsOrNullObject(cellTypeString: "ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible", cellValueTypeString?: "All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"): Excel.RangeAreas;Parameters
- cellTypeString
-
"ConditionalFormats" | "DataValidations" | "Blanks" | "Constants" | "Formulas" | "SameConditionalFormat" | "SameDataValidation" | "Visible"
The type of cells to include.
- cellValueTypeString
-
"All" | "Errors" | "ErrorsLogical" | "ErrorsNumbers" | "ErrorsText" | "ErrorsLogicalNumber" | "ErrorsLogicalText" | "ErrorsNumberText" | "Logical" | "LogicalNumbers" | "LogicalText" | "LogicalNumbersText" | "Numbers" | "NumbersText" | "Text"
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
Remarks
getTables(fullyContained)
Returns a scoped collection of tables that overlap with any range in this RangeAreas object.
getTables(fullyContained?: boolean): Excel.TableScopedCollection;Parameters
- fullyContained
-
boolean
If true, returns only tables that are fully contained within the range bounds. Default is false.
Returns
Remarks
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, the ItemNotFound error will be thrown.
getUsedRangeAreas(valuesOnly?: boolean): Excel.RangeAreas;Parameters
- valuesOnly
-
boolean
Whether to only consider cells with values as used cells. Default is false.
Returns
Remarks
getUsedRangeAreasOrNullObject(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 an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.
getUsedRangeAreasOrNullObject(valuesOnly?: boolean): Excel.RangeAreas;Parameters
- valuesOnly
-
boolean
Whether to only consider cells with values as used cells.
Returns
Remarks
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.RangeAreasLoadOptions): Excel.RangeAreas;Parameters
Provides options for which properties of the object to load.
Returns
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.RangeAreas;Parameters
- propertyNames
-
string | string[]
A comma-delimited string or an array of strings that specify the properties to load.
Returns
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.RangeAreas;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
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.RangeAreasUpdateData, options?: OfficeExtension.UpdateOptions): void;Parameters
- properties
- Excel.Interfaces.RangeAreasUpdateData
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.RangeAreas): void;Parameters
- properties
- Excel.RangeAreas
Returns
void
setDirty()
Sets the RangeAreas to be recalculated when the next recalculation occurs.
setDirty(): void;Returns
void
Remarks
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.RangeAreas object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.RangeAreasData) that contains shallow copies of any loaded child properties from the original object.
toJSON(): Excel.Interfaces.RangeAreasData;Returns
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.RangeAreas;Returns
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.RangeAreas;Returns
Office Add-ins