How to: Access a Range of Cells

• Jan 13, 2020
• 4 minutes to read

The table below describes how to access a cell range in a worksheet.

Task	Use one of the following members
Access a cell range by its reference in the A1 style or name.	Worksheet.Item IWorkbook.Range.Item Worksheet.Range.Item IWorkbook.Range.Parse Worksheet.Range.Parse
Access a cell range by its reference in the R1C1 style.	IWorkbook.Range.Parse Worksheet.Range.Parse
Access a cell range by row and column indexes.	IWorkbook.Range.FromLTRB Worksheet.Range.FromLTRB
Create a union cell range.	CellRange.Union IWorkbook.Range.Union Worksheet.Range.Union
Access subranges of a union cell range.	CellRange.Areas

View Example

CellActions.cs

// Access the cell range A1:B5.
CellRange rangeA1B5 = worksheet["A1:B5"];

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

// Access the cell E7.
CellRange rangeE7 = worksheet["E7"];

// Access the A column.
CellRange rangeColumnA = worksheet["A:A"];

// Access the 5th row.
CellRange rangeRow5 = worksheet["5:5"];

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

// Obtain the cell 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 cell range A1:D3.
CellRange rangeA1D3 = worksheet.Range.FromLTRB(0, 0, 3, 2);

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

// Define a name for the cell range D20:G23.
worksheet.DefinedNames.Add("MyNamedRange", "Sheet1!$D$20:$G$23");
// Access the cell 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"];

// Use the CellRange.Union method to create a complex range.
CellRange complexRange1 = worksheet["B7:C9"].Union(rangeD5E7);

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

// Create a complex range from multiple cell ranges separated by commas.
CellRange complexRange3 = worksheet["D15:F18, G19:H20, I21"];

// Fill the created ranges with different colors.
complexRange1.FillColor = Color.LightBlue;
complexRange2.FillColor = Color.LightGreen;
complexRange3.FillColor = Color.LightPink;

// Use the Areas property to get access to a complex range's component.
complexRange2.Areas[2].Borders.SetOutsideBorders(Color.DarkGreen, BorderLineStyle.Medium);

CellActions.vb

' Access the cell range A1:B5.
Dim rangeA1B5 As CellRange = worksheet("A1:B5")

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

' Access the cell E7.
Dim rangeE7 As CellRange = worksheet("E7")

' Access the A column.
Dim rangeColumnA As CellRange = worksheet("A:A")

' Access the 5th row.
Dim rangeRow5 As CellRange = worksheet("5:5")

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

' Obtain the cell 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 cell range A1:D3.
Dim rangeA1D3 As CellRange = worksheet.Range.FromLTRB(0, 0, 3, 2)

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

' Define a name for the cell range D20:G23.
worksheet.DefinedNames.Add("MyNamedRange", "Sheet1!$D$20:$G$23")
' Access the cell 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")

' Use the CellRange.Union method to create a complex range.
Dim complexRange1 As CellRange = worksheet("B7:C9").Union(rangeD5E7)

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

' Create a complex range from multiple cell ranges separated by commas.
Dim complexRange3 As CellRange = worksheet("D15:F18, G19:H20, I21")

' Fill the created ranges with different colors.
complexRange1.FillColor = Color.LightBlue
complexRange2.FillColor = Color.LightGreen
complexRange3.FillColor = Color.LightPink

' Use the Areas property to get access to a complex range's component.
complexRange2.Areas(2).Borders.SetOutsideBorders(Color.DarkGreen, BorderLineStyle.Medium)