Working with the Range Object

The Range object represents a range of cells in a spreadsheet. A range can contain one cell, multiple contiguous cells, and even multiple discontiguous cells. You can select multiple discontiguous cells by holding down the Ctrl key as you select in Excel.

Getting a Range Object for a Particular Cell or Range of Cells

Excel provides a variety of ways to get a Range object. The Range object is the object you use when you want to work with a cell or range of cells in an Excel worksheet. Two ways to get a Range object were mentioned in the description of the Application object. Application.ActiveCell returns the top-left cell of the active selection in the active window. Application.Selection returns an object that represents the active selection in the active window. If the active selection is a range of cells, you can cast Application.Selection to a Range object. If something else is selected in the active window, such as a shape or a chart, Application.Selection returns that selected object instead.

Worksheet also provides several ways to get a Range object. The Worksheet.get_Range method is the most common way to get a Range object from a Worksheet. This method takes a required object parameter to which you can pass a string. It has a second optional parameter to which you can pass a second string. The strings you pass are in what is called A1-style reference format. The easiest way to explain the A1-style reference format is to give several examples.

The reference A1 specifies the cell at row 1, column A. The reference D22 specifies the cell at row 22, column D. The reference AA11 specifies the cell at row 11, column AA (column 27).

The reference $A$1 also refers to the cell at row 1, column A. If you use $ signs in an A1-style reference, they are ignored.

You can use the range operator (:) to specify a range of cells where the first A1-style reference is the top-left corner of the range followed by a colon operator followed by a second A1-style reference for the bottom-right corner of the range. The reference A1:B1 refers to the two cells at row 1, column A and row 1, column B. The reference A1:AA11 refers to all 297 cells in the block whose top-left corner is at row 1, column A and bottom-right corner is at row 11, column AA (column 27).

You can use the union operator (,) to specify multiple cells that could be discontiguous. For example, the reference A1,C4 specifies a range of two cells where the first cell is at row 1, column A and the second cell is at row 4, column C. Users can select discontiguous ranges of cells by holding down the Ctrl key as they select various cells. The reference A1,C4,C8,C10 is another valid A1-style reference that specifies four different cells.

The intersection operator (a space) lets you specify the intersection of cells. For example, the reference A1:A10 A5:A15 resolves to the intersecting six cells starting at row 5, column A and ending at row 10, column A. The reference A1:A10 A5:A15 A5 resolves to the single cell at row 5, column A.

You can also use any names you have defined in the worksheet in your A1-style reference. For example, suppose that you defined a named range called foo that refers to the cell A1. Some valid A1-style references using your name would include foo:A2, which refers to the cells at row 1, column A and row 2, column A. The reference foo,A5:A6 refers to the cells at row 1, column A; row 5, column A; and row 6, column A.

As mentioned earlier, the get_Range method takes a second optional parameter to which you can pass a second A1-style reference string. The first parameter and the second parameter are effectively combined using the range operator. So the range that get_Range returns when you call get_Range("A1", "A2") is equivalent to the range you get when you call get_Range("A1:A2", Type.Missing).

A second way to get a Range object is by using the Worksheet.Cells property, which returns a Range for all the cells in the worksheet. You can then use the same get_Range method on the returned Range object and pass A1-style references to select cells in the same way you do using get_Range from the Worksheet object. So Cells.get_Range("A1:A2", Type.Missing) is equivalent to get_Range("A1:A2", Type.Missing). A more common use of the Cells property is to use it in conjunction with Range's get_Item property, which takes a row index and an optional column index. Using get_Item is a way to get to a particular cell without using the A1-style reference. So Cells.get_Item(1,1) is equivalent to get_Range("A1", Type.Missing).

Another way to get a Range object is by using the Worksheet.Rows or Worksheet.Columns properties. These return a Range that acts differently than other Range objects. For example, if you take the Range returned by Columns and display the count of cells in the range, it returns 256the number of columns. But if you call the Select method on the returned Range, Excel selects all 16,772,216 cells in the worksheet. The easiest way to think of the ranges returned by Rows and Columns is that they behave similarly to how column and row headings behave in Excel.

Listing 5-27 shows several examples of using the get_Range method and the Cells, Rows, and Columns properties. We use the Value2 property of range to set every cell in the range to the string value specified. Figure 5-7 shows the result of running the program in Listing 5-27.

Listing 5-27. A VSTO Customization That Gets Range Objects

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range r1 = this.get_Range("A1", missing); r1.Value2 = "r1"; Excel.Range r2 = this.get_Range("B7:C9", missing); r2.Value2 = "r2"; Excel.Range r3 = this.get_Range("C1,C3,C5", missing); r3.Value2 = "r3"; Excel.Range r4 = this.get_Range("A1:A10 A5:A15", missing); r4.Value2 = "r4"; Excel.Range r5 = this.get_Range("F4", "G8"); r5.Value2 = "r5"; Excel.Range r6 = this.Rows.get_Item(12, missing) as Excel.Range; r6.Value2 = "r6"; Excel.Range r7 = this.Columns.get_Item(5, missing) as Excel.Range; r7.Value2 = "r7"; }

Figure 5-7. Result of running Listing 5-27.

Working with Addresses

Given a Range object, you often need to determine what cells it refers to. The get_Address method returns an address for the range in either A1 style or R1C1 style. You have already learned about A1-style references. R1C1-style references support all the same operators as discussed with A1-style references (colon for range, comma for union, and space for intersection). R1C1-style references have row and column numbers prefaced by R and C respectively. So cell A4 in R1C1 style would be R4C1. Figure 5-8 shows a range that consists of three areas that we consider in this section.

Figure 5-8. A range with three discontiguous areas.

The address for the range in Figure 5-8 is shown here in A1 style and in R1C1 style:

$A$15:$F$28,$H$3:$J$9,$L$1 R15C1:R28C6,R3C8:R9C10,R1C12

Another option when getting an address is whether to get an external reference or a local reference. The addresses we have already shown for Figure 5-8 are local references. An external reference includes the name of the workbook and sheet where the range is. Here is the same range in Figure 5-8 expressed as an external reference in A1 style and R1C1 style.

[Book1]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1 [Book1]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12

For our example, the workbook we created the range in was not saved. When we save it as Book1.xls, the addresses look like this:

[Book1.xls]Sheet1!$A$15:$F$28,$H$3:$J$9,$L$1 [Book1.xls]Sheet1!R15C1:R28C6,R3C8:R9C10,R1C12

Another option when getting an address is whether to use an absolute address or a relative one. The addresses we have already considered have been absolute. The same addresses in relative format (relative to cell A1) look like this:

R[14]C:R[27]C[5],R[2]C[7]:R[8]C[9],RC[11] A15:F28,H3:J9,L1

For an R1C1-style address, you can also specify the cell you want your address to be relative to. If we get an R1C1-style for our range in Figure 5-4 relative to cell B2, we get the following result:

R[13]C[-1]:R[26]C[4],R[1]C[6]:R[7]C[8],R[-1]C[10]

The get_Address method takes five optional parameters that control the way the reference is returned, as described in Table 5-17.

Listing 5-28 shows several examples of using get_Address with our example range.

Listing 5-28. A VSTO Customization That Uses get_Address

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range( "$A$15:$F$28,$H$3:$J$9,$L$1", missing); System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.AppendLine("A1-Style Addresses:"); sb.AppendFormat("Default: {0} ", range1.get_Address( missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing)); sb.AppendFormat("Relative rows: {0} ", range1.get_Address(false, missing, Excel.XlReferenceStyle.xlA1, missing, missing)); sb.AppendFormat("Row & Column Relative: {0} ", range1.get_Address(false, false, Excel.XlReferenceStyle.xlA1, missing, missing)); sb.AppendFormat("External: {0} ", range1.get_Address( missing, missing, Excel.XlReferenceStyle.xlA1, true, missing)); sb.AppendLine(); sb.AppendLine("R1C1-Style Addresses:"); sb.AppendFormat("Default: {0} ", range1.get_Address( missing, missing, Excel.XlReferenceStyle.xlR1C1, missing, missing)); sb.AppendFormat("Row & Column Relative to C5: {0} ", range1.get_Address(false, false, Excel.XlReferenceStyle.xlR1C1, missing, this.get_Range("C5", missing))); sb.AppendFormat("External: {0}", range1.get_Address( missing, missing, Excel.XlReferenceStyle.xlR1C1, true, missing)); MessageBox.Show(sb.ToString()); }

Creating New Ranges Using Operator Methods

We have discussed several "operators" that can be used in address strings, including the union operator (a comma) and the intersection operator (a space). You can also apply these operators through the Application.Union and Application.Intersection methods.

It is also possible to take a Range and get a new Range that is offset from it by some number of rows and columns by using the get_Offset method. This method takes a row and column value to offset the given range by and returns the newly offset range. So calling get_Offset(5, 5) on the example range in Figure 5-8 returns a range with this A1-style address:

"$F$20:$K$33,$M$8:$O$14,$Q$6"

Listing 5-29 shows an example of using these operators. Note that Union and Intersection take a lot of optional parameters, allowing you to union or intersect more than just two ranges.

Listing 5-29. A VSTO Customization That Uses Union, Intersection, and get_Offset

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Application app = this.Application; Excel.Range range1 = this.get_Range("$A$15:$F$28", missing); Excel.Range range2 = this.get_Range("$H$3:$J$9", missing); Excel.Range range3 = this.get_Range("$L$1", missing); Excel.Range range4 = this.get_Range("$A$11:$G$30", missing); Excel.Range rangeUnion = app.Union(range1, range2, range3, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Excel.Range rangeIntersection = app.Intersect(range1, range4, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Excel.Range rangeOffset = rangeUnion.get_Offset(5, 5); MessageBox.Show(String.Format("Union: {0}", rangeUnion.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))); MessageBox.Show(String.Format("Intersection: {0}", rangeIntersection.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))); MessageBox.Show(String.Format("Offset: {0}", rangeOffset.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))); }

Working with Areas

When there are multiple discontiguous ranges of cells in one Range, each discontiguous range is called an area. If there are multiple discontiguous areas in the Range, use the Areas property to access the each area (as a Range) via the Areas collection. The Areas collection has an Areas.Count property, and an Areas.get_Item method that takes an int parameter representing the 1-based index into the array. Listing 5-30 shows an example of iterating over our example range (which has three areas) and printing the address of each area.

Listing 5-30. A VSTO Customization That Works with Areas

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range( "$A$15:$F$28,$H$3:$J$9,$L$1", missing); MessageBox.Show(String.Format("There are {0} areas", range1.Areas.Count)); foreach (Excel.Range area in range1.Areas) { MessageBox.Show(String.Format("Area address is {0}", area.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing))); } }

Working with Cells

The Count property returns the number of cells in a given Range. You can get to a specific single-cell Range within a Range by using the get_Item method. The get_Item method takes a required row index and an optional column index. The column index can be omitted when the range is a one-dimensional array of cells because it only has cells from one column or one rowin this case, the parameter called RowIndex really acts like an array index. If the Range has multiple areas, you must get the area you want to work with firstotherwise, get_Item only returns cells out of the first area in the Range.

Listing 5-31 shows an example of using get_Item.

Listing 5-31. A VSTO Customization That Uses get_Item

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range("$A$15:$F$28", missing); int rowCount = range1.Rows.Count; int columnCount = range1.Columns.Count; for (int i = 1; i <= rowCount; i++) { for (int j = 1; j <= columnCount; j++) { Excel.Range cell = range1.get_Item(i, j) as Excel.Range; string address = cell.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing); cell.Value2 = String.Format("get_Item({0},{1})", i, j); } } }

Working with Rows and Columns

Given a Range object, you can determine the row and column number of the top-left corner of its first area using the Row and Column properties. The row and column number are returned as int values.

You can also determine the total number of rows and columns in the first area using the Rows and Columns properties. These properties return special ranges that you can think of as corresponding to the row or column headers associated with the range. When we get Rows.Count from our example range in Figure 5-8, it returns 14, and Columns.Count returns 6. This makes sense because the first area in our selection (A15:F28) spans 6 columns and 14 rows.

To get the row and column position of the bottom-right corner of the first area, you can use the rather awkward expressions shown in Listing 5-32. Listing 5-32 also illustrates the use of get_Item, which takes the row and column index (relative to the top of the given range) and returns the cell (as a Range) at that row and column index. When you get a Rows or a Columns range, these ranges are one-dimensionalhence the parameter called RowIndex acts like an array index in this case.

Listing 5-32. A VSTO Customization That Gets Row and Column Positions

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range( "$A$15:$F$28,$H$3:$J$9,$L$1", missing); Excel.Range area = range1.Areas.get_Item(1); int topLeftColumn = area.Column; int topLeftRow = area.Row; int bottomRightColumn = ((Excel.Range)area.Columns. get_Item(area.Columns.Count, missing)).Column; int bottomRightRow = ((Excel.Range)area.Rows. get_Item(area.Rows.Count, missing)).Row; MessageBox.Show(String.Format( "Area Top Left Column {0} and Row {1}", topLeftColumn, topLeftRow)); MessageBox.Show(String.Format( "Area Bottom Right Column {0} and Row {1}", bottomRightColumn, bottomRightRow)); MessageBox.Show(String.Format( "Total Rows in Area = {0}", area.Rows)); MessageBox.Show(String.Format( "Total Columns in Area = {0}", area.Columns)); }

Working with Regions

The CurrentRegion property returns a Range that is expanded to include all cells up to a blank row and blank column. This expanded Range is called a region. So, for example, you might have a Range that includes several cells in a tableto get a Range that encompasses the entire table (assuming the table is bordered by blank rows and columns) you would use the CurrentRegion property on the smaller Range to return the entire table.

The get_End method is a method that works against the region associated with a Range. The get_End method takes a member of the XlDirection enumeration: either xlDown, xlUp, xlToLeft, or xlToRight. This method when passed xlUp returns the topmost cell in the region in the same column as the top-left cell of the Range. When passed xlDown, it returns the bottom-most cell in the region in the same column as the top left cell of the Range. When passed xlToLeft, it returns the leftmost cell in the region in the same row as the top-left cell of the Range. And when passed xlToRight, it returns the rightmost cell in the region in the same row as the top-left cell of the Range.

Selecting a Range

You can make a range the current selection using the Select method on a Range. Remember that calling Select changes the user's current selection, which is not a very nice thing to do without good reason. In some cases, however, you want to draw the user's attention to something, and in those cases selecting a Range is reasonable to do.

Editing the Values in a Range

Two methods are typically used to get and set the values in a range. The first way is to use the methods get_Value and set_Value. The second way is to use the property Value2. Value2 and get_Value differ in that the Value2 property returns cells that are currency or dates as a double value. Also, get_Value takes an optional parameter of type XlRangeValueDataType. If you pass XlRangeValueData.xlRangeValueDefault, you will get back an object representing the value of the cell for a single cell Range. For both Value2 and get_Value, if the Range contains multiple cells, you will get back an array of objects corresponding to the cells in the Range.

Listing 5-33 shows several examples of using Value2 including an example of passing an array of values to Value2. Setting the values of the cells in a Range all at once via an array is more efficient than making multiple calls to set each cell individually.

Listing 5-33. A VSTO Customization That Uses Value2

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range("$A$15:$F$28", missing); range1.Value2 = "Test"; int rowCount = range1.Rows.Count; int columnCount = range1.Columns.Count; object[,] array = new object[rowCount, columnCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { array[i, j] = i * j; } } range1.Value2 = array; }

Copying, Clearing, and Deleting Ranges

Excel provides a number of methods to copy, clear, and delete a Range. The Copy method takes a Destination parameter that you can pass the destination of the copied range. The Clear method clears the content and formatting of the cells in the range. ClearContents clears just the values of the cells in the range, and ClearFormats clears just the formatting. The Delete method deletes the range of cells and takes as a parameter the direction in which to shift cells to replace deleted cells. The direction is passed as a member of the XlDeleteShiftDirection enumeration: xlShiftToLeft or xlShiftUp.

Finding Text in a Range

The Find method allows you to find text in a Range and return the cell within the Range where the text is found. The Find method corresponds to the Find and Replace dialog shown in Figure 5-9. If you omit parameters when calling the Find method, it uses whatever settings were set by the user the last time the Find dialog was used. Furthermore, when you specify the parameters, the settings you specified appear in the Find dialog the next time the user opens it.

Figure 5-9. The Find and Replace dialog.

The Find method takes a number of parameters described in Table 5-18. Find returns a Range object if it succeeds and null if it fails to find anything. You can find the next cell that matches your find criteria by using the FindNext method. FindNext takes an optional After parameter to which you need to pass the last found Range to ensure you do not just keep finding the same cell over and over again. Listing 5-34 shows an example of using the Find and FindNext method where we search for any cells containing the character "2" and bold those cells.

Listing 5-34. A VSTO Customization That Uses Find and FindNext

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range("$A$15:$F$28", missing); int rowCount = range1.Rows.Count; int columnCount = range1.Columns.Count; object[,] array = new object[rowCount, columnCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { array[i, j] = i * j; } } range1.Value2 = array; Excel.Range foundRange = range1.Find("2", range1.get_Item(1, 1), missing, Excel.XlLookAt.xlPart, missing, Excel.XlSearchDirection.xlNext, missing, missing, missing); while (foundRange != null) { foundRange.Font.Bold = true; foundRange = range1.FindNext(foundRange); } }

Formatting a Range of Cells

Excel provides several methods and properties to format a range of cells. Among the most useful is the NumberFormat property that you can set to format strings corresponding to the strings in the Custom category of the Format Cells dialog. For example, you can set NumberFormat to General to set no specific number format. Setting NumberFormat to m/d/yyyy sets a date format, and 0% sets the format to a percentage format. When using NumberFormat, be sure to consider the locale issue discussed in the section "Special Excel Issues"later in this chapter if you are building a console application or an add-in, because reading and setting this string can cause problems when running in different locales. If you are using a VSTO code behind a workbook or template project, you do not have to worry about the locale issue.

The Font property returns a Font object that can be used to set the Font to various sizes and styles. Listing 5-34 showed an example of the Font object used to bold the font of a cell.

Excel also enables you to create styles associated with a Workbook and apply those styles to a Range. You can create styles using Workbook.Styles. Listing 5-35 shows an example of creating a style and applying it to a Range.

Listing 5-35. A VSTO Customization That Creates and Applies Styles

private void Sheet1_Startup(object sender, System.EventArgs e) { Excel.Range range1 = this.get_Range("$A$15:$F$28", missing); range1.Value2 = "Hello"; Excel.Style style = Globals.ThisWorkbook.Styles.Add( "My Style", missing); style.Font.Bold = true; style.Borders.LineStyle = Excel.XlLineStyle.xlDash; style.Borders.ColorIndex = 3; style.NumberFormat = "General"; range1.Style = "My Style"; }