Worksheet.Range Property

Provides access to the range of cells in the worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

IRangeProvider Range { get; }

VB.NET

ReadOnly Property Range As IRangeProvider

Property Value

Type	Description
IRangeProvider	An object implementing the IRangeProvider interface.

Remarks

A cell range is a rectangular block of cells that is specified by the CellRange object. You can manipulate cell ranges when you format cells or process data.

The Range property returns the IRangeProvider object. Use the one of the following members to obtain a cell range.

• IRangeProvider.Item, IRangeProvider.Parse - obtain a cell range by its cell reference or name.
• IRangeProvider.FromLTRB - obtains a cell range by the indexes of its top left and bottom right cells.

Use the following properties and methods to work with cell ranges.

Operation	Members	Read More…
Get Cell Reference	CellRange.GetReferenceA1 CellRange.GetReferenceR1C1	Cell Referencing
Format Cell Ranges	CellRange.Style CellRange.BeginUpdateFormatting - CellRange.EndUpdateFormatting CellRange.FillColor, Formatting.Fill Formatting.Font Formatting.NumberFormat Formatting.Alignment Formatting.Borders, CellRange.SetInsideBorders	How to: Format Cells
Set Values or Formulas to Cells	CellRange.Value CellRange.Formula, CellRange.ArrayFormula, CellRange.HasArrayFormula	Cell Data Types Spreadsheet Formulas How to: Change a Cell or Cell Range Value How to: Add Formulas to Cells
Add Hyperlinks to Cells	Worksheet.Hyperlinks HyperlinkCollection.Add HyperlinkCollection.GetHyperlinks	How to: Add a Hyperlink to a Cell
Add Comments to Cells	Worksheet.Comments CommentCollection.Add CommentCollection.GetComments	Comments
Name Cells	CellRange.Name Worksheet.DefinedNames, DefinedNameCollection.Add	Defined Names How to: Create a Named Range of Cells
Merge Cells	Worksheet.MergeCells, Worksheet.UnMergeCells	How to: Merge Cells or Split Merged Cells
Copy Cells	CellRange.CopyFrom	How to: Copy Cell Data Only, Cell Style Only, or Cell Data with Style
Clear Cells	Worksheet.Clear Worksheet.ClearContents Worksheet.ClearFormats Worksheet.ClearComments Worksheet.ClearHyperlinks	How to: Clear Cells of Content, Formatting, Hyperlinks and Comments
Insert Cells	Worksheet.InsertCells	How to: Insert a Cell or Cell Range
Delete Cells	Worksheet.DeleteCells	How to: Delete a Cell or Range of Cells
Obtain Intersection of Cell Ranges	CellRange.IsIntersecting CellRange.Intersect
Create a Complex Range	CellRange.Union

Example

This example demonstrates how to access ranges of cells in a worksheet. There are several ways to accomplish this.

• The Worksheet.Item property obtains cell ranges defined by a cell reference (using A1 style) or a defined name.
• Ranges defined by a cell reference (using R1C1 or other reference styles), a defined name in a workbook, or by indexes of the bounding rows and columns - use the IRangeProvider.Item, IRangeProvider.Parse and IRangeProvider.FromLTRB members. Access the IRangeProvider object by the Worksheet.Range or IWorkbook.Range property.

View Example

CellActions.cs

// A range that includes cells from the top left cell (A1) to the bottom right cell (B5).
CellRange rangeA1B5 = worksheet["A1:B5"];

// A rectangular range that includes cells from the top left cell (C5) to the bottom right cell (E7).
CellRange rangeC5E7 = worksheet["C5:E7"];

// The C4:E7 cell range located in the "Sheet3" worksheet.
CellRange rangeSheet3C4E7 = workbook.Range["Sheet3!C4:E7"];

// A range that contains a single cell (E7).
CellRange rangeE7 = worksheet["E7"];

// A range that includes the entire column A.
CellRange rangeColumnA = worksheet["A:A"];

// A range that includes the entire row 5.
CellRange rangeRow5 = worksheet["5:5"];

// A minimal rectangular range that includes all listed cells: C6, D9 and E7.
CellRange rangeC6D9E7 = worksheet.Range.Parse("C6:D9:E7");

// A rectangular range whose left column index is 0, top row index is 0, 
// right column index is 3 and bottom row index is 2. This is the A1:D3 cell range.
CellRange rangeA1D3 = worksheet.Range.FromLTRB(0, 0, 3, 2);

// A range that includes the intersection of two ranges: C5:E10 and E9:G13. 
// This is the E9:E10 cell range.
CellRange rangeE9E10 = worksheet["C5:E10 E9:G13"];

// Create a defined name for the D20:G23 cell range.
worksheet.DefinedNames.Add("MyNamedRange", "Sheet1!$D$20:$G$23");
// Access a range by its defined name.
CellRange rangeD20G23 = worksheet["MyNamedRange"];
CellRange rangeA1D4 = worksheet["A1:D4"];
CellRange rangeD5E7 = worksheet["D5:E7"];
CellRange rangeRow11 = worksheet["11:11"];
CellRange rangeF7 = worksheet["F7"];

// Create a complex range using the Range.Union method.
CellRange complexRange1 = worksheet["A7:A9"].Union(rangeD5E7);

// Create a complex range using the IRangeProvider.Union method.
CellRange complexRange2 = worksheet.Range.Union(new CellRange[] { rangeRow11, rangeA1D4, rangeF7 });

// Fill the ranges with different colors.
complexRange1.FillColor = myColor1;
complexRange2.FillColor = myColor2;

// Use the Areas property to get access to a component of a complex range.
complexRange2.Areas[2].FillColor = Color.Beige;

CellActions.vb

' A range that includes cells from the top left cell (A1) to the bottom right cell (B5).
Dim rangeA1B5 As CellRange = worksheet("A1:B5")

' A rectangular range that includes cells from the top left cell (C5) to the bottom right cell (E7).
Dim rangeC5E7 As CellRange = worksheet("C5:E7")

' The C4:E7 cell range located in the "Sheet3" worksheet.
Dim rangeSheet3C4E7 As CellRange = workbook.Range("Sheet3!C4:E7")

' A range that contains a single cell (E7).
Dim rangeE7 As CellRange = worksheet("E7")

' A range that includes the entire column A.
Dim rangeColumnA As CellRange = worksheet("A:A")

' A range that includes the entire row 5.
Dim rangeRow5 As CellRange = worksheet("5:5")

' A minimal rectangular range that includes all listed cells: C6, D9 and E7.
Dim rangeC6D9E7 As CellRange = worksheet.Range.Parse("C6:D9:E7")

' A rectangular range whose left column index is 0, top row index is 0, 
' right column index is 3 and bottom row index is 2. This is the A1:D3 cell range.
Dim rangeA1D3 As CellRange = worksheet.Range.FromLTRB(0, 0, 3, 2)

' A range that includes the intersection of two ranges: C5:E10 and E9:G13. 
' This is the E9:E10 cell range.
Dim rangeE9E10 As CellRange = worksheet("C5:E10 E9:G13")

' Create a defined name for the D20:G23 cell range.
worksheet.DefinedNames.Add("MyNamedRange", "Sheet1!$D$20:$G$23")
' Access a range by its defined name.
Dim rangeD20G23 As CellRange = worksheet("MyNamedRange")
Dim rangeA1D4 As CellRange = worksheet("A1:D4")
Dim rangeD5E7 As CellRange = worksheet("D5:E7")
Dim rangeRow11 As CellRange = worksheet("11:11")
Dim rangeF7 As CellRange = worksheet("F7")

' Create a complex range using the Range.Union method.
Dim complexRange1 As CellRange = worksheet("A7:A9").Union(rangeD5E7)

' Create a complex range using the IRangeProvider.Union method.
Dim complexRange2 As CellRange = worksheet.Range.Union(New CellRange() { rangeRow11, rangeA1D4, rangeF7 })

' Fill the ranges with different colors.
complexRange1.FillColor = myColor1
complexRange2.FillColor = myColor2

' Use the Areas property to get access to a component of a complex range.
complexRange2.Areas(2).FillColor = Color.Beige

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the Range property.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

winforms-spreadsheet-apply-conditional-formatting-to-a-range-of-cells/CS/SpreadsheetControl/SpreadsheetActions/ConditionalFormatting.cs#L38

// Create the rule highlighting values that are above the average in cells C2 through C15.
AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual);
// Specify formatting options to be applied to cells if the condition is true.

wpf-spreadsheet-how-to-apply-conditional-formatting-to-a-range-of-cells/CS/ConditionalFormatting_WPF_Examples/SpreadsheetActions/ConditionalFormatting.cs#L37

// Create the rule highlighting values that are above the average in cells C2 through C15.
AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual);
// Specify formatting options to be applied to cells if the condition is true.

spreadsheet-document-api-apply-conditional-formatting-to-cell-range/CS/ConditionalFormatting_Example/SpreadsheetActions/ConditionalFormatting.cs#L55

// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter.";

spreadsheet-document-api-examples-part1/CS/SpreadsheetExamples/SpreadsheetActions/DocumentPropertiesActions.cs#L22

CellRange header = worksheet.Range["B2:C2"];
header[0].Value = "Property Name";

winforms-spreadsheetcontrol-api-part-2/CS/SpreadsheetControl_API_Part02/SpreadsheetActions/DocumentPropertiesActions.cs#L23

CellRange header = worksheet.Range["B2:C2"];
header[0].Value = "Property Name";

VB.NET

winforms-spreadsheet-apply-conditional-formatting-to-a-range-of-cells/VB/SpreadsheetControl/SpreadsheetActions/ConditionalFormatting.vb#L37

' Create the rule highlighting values that are above the average in cells C2 through C15.
Dim cfRule1 As AverageConditionalFormatting = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range("$C$2:$C$15"), ConditionalFormattingAverageCondition.AboveOrEqual)
' Specify formatting options to be applied to cells if the condition is true.

wpf-spreadsheet-how-to-apply-conditional-formatting-to-a-range-of-cells/VB/ConditionalFormatting_WPF_Examples/SpreadsheetActions/ConditionalFormatting.vb#L35

' Create the rule highlighting values that are above the average in cells C2 through C15.
Dim cfRule1 As AverageConditionalFormatting = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range("$C$2:$C$15"), ConditionalFormattingAverageCondition.AboveOrEqual)
' Specify formatting options to be applied to cells if the condition is true.

spreadsheet-document-api-apply-conditional-formatting-to-cell-range/VB/ConditionalFormatting_Example/SpreadsheetActions/ConditionalFormatting.vb#L60

' Add an explanation to the created rule.
Dim ruleExplanation As CellRange = worksheet.Range("A17:G18")
ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter."

spreadsheet-document-api-examples-part1/VB/SpreadsheetExamples/SpreadsheetActions/DocumentPropertiesActions.vb#L22

worksheet("E6").Value = "Mike Hamilton"
Dim header As CellRange = worksheet.Range("B2:C2")
header(0).Value = "Property Name"

winforms-spreadsheetcontrol-api-part-2/VB/SpreadsheetControl_API_Part02/SpreadsheetActions/DocumentPropertiesActions.vb#L19

Dim header As CellRange = worksheet.Range("B2:C2")
header(0).Value = "Property Name"