Referring to a Range

A macro that needs to work with ranges of differing sizes must be flexible. In this section, you’ll learn various ways to refer to a range. The examples in this section don’t do anything except reference ranges within a list, but these are all techniques you’ll use many times as you work with ranges. Later in the chapter, you’ll use these techniques in more practical contexts.

Refer to a Range by Using an Address

The Range property is a useful and flexible way of retrieving a reference to a range. The Range property allows you to specify the address of the range you want. You can use the Object Browser to see how to use the Range property.

1. In the Chapter04 workbook, right-click a worksheet tab, and then click View Code on the shortcut menu to display the Visual Basic editor.

   Rearrange the Excel and Visual Basic editor windows so that you can see them side by side.

2. In the Visual Basic editor, click the Object Browser toolbar button.

   
   Object Browser

   See Also 

   If you want to change the Object Browser into a dockable window, see the sidebar titled “Dockable Views” in Chapter 3, “Explore Workbooks and Worksheets.”

The Object Browser appears in the space normally held by the code window.

In essence, the Object Browser consists of two lists. The list on the left is a listof object class names. The list on the right is a list of members-methods and properties-available for the currently selected object class. At the top of the list of classes is a special object class named <globals>. The <globals> object is not a real object class, but it includes in its list of members all the methods and properties you can use without specifying an object. These are the methods and properties you use to start a statement.

1. In the Classes list, select the <globals> object, click in the Members of '<globals>' list, and press the R key to scroll to the first member that begins with the letter R. Then select the Range property.

   The box at the bottom of the Object Browser displays information about the Range property. This property takes two arguments. The brackets around the second argument indicate that it is optional. The Range property returns a reference to a Range object.

   

2. Right-click the Range property name in the Members list, and click Copy on the shortcut menu.

3. Click the View menu, and click Immediate Window.

4. Right-click the Immediate window, and click Paste.

   This is equivalent to using the Complete Word command to enter the function name.

5. After the Range property, type an opening parenthesis (Visual Basic will display the argument list), and then type "B2" followed by a closing parenthesis and a period. Then type Select.

   The complete statement is Range("B2").Select. You need the quotation marks around the range definition because this is the name of the range, not the item number of a member of a collection.

6. Press Enter to select cell B2 on the active worksheet.

7. Type Range("B2:H2").Select and press Enter.

   The first argument of the range property can be a multicell range. In fact, it can be anything that Excel recognizes.

   

8. Type Range("H14").Select and press Enter to select the lower-right corner of the list of values. Then type Range(Selection, "B2").Select and press Enter.

   This selects the range from cell H14 (the current selection) to cell B2 (the upper left cell of the list). The arguments to the Range property do not have to be strings; they can also be references to range objects. A common use of the two-argument form of the Range property is to select the range that extends from the currently selected range to some fixed point at the top of the worksheet.

9. Type ?Selection.Count and press Enter.

   The number 91 appears in the Immediate window. There are 91 cells in the currently selected range. If you don’t specify otherwise, Excel treats a range object as a collection of cells. If you want to know the number of rows or columns in the range, you can do that by using specialized properties, as you will learn in the section titled “Refer to a Range as a Collection of Rows or Columns,” later in this chapter.

   Tip 

   As you learned in Chapter 3, typing a question mark before an expression in the Immediate window allows you to display the value of that expression.

The Range property is a flexible way of establishing a link to an arbitrary Range object. You can use either a single text string that contains any valid reference as an argument to the Range property or two arguments that define the end points of a rectangular range. Once you have the resulting reference to a range object, you can use any of the members that appear in Object Browser for the Range class.

Refer to a Range as a Collection of Cells

Multiple worksheets can exist in a workbook, and the Worksheets collection is defined as an object class. A Worksheets object has a list of methods and properties that is separate from a Worksheet object.

Similarly, multiple cells exist on a worksheet. You might expect that Excel would have a Cells collection object. But a collection of cells is more complicated than a collection of worksheets because cells come in two dimensions-rows and columns. For example, you can think of the range A1:B3 as a collection of six cells, as a collection of three rows, or as a collection of two columns.

Excel therefore has three properties that look at a range as a collection. The first of these-the Cells property-returns a collection of cells. However, this is not a separate class. The result of the Cells property is still a Range object, and it can use any of the methods or properties of any other Range object. Because Excel thinks of any range, by default, as a collection of cells, you typically use the Cells property as an alternative to the Range property-using numbers, rather than text strings.

1. In the Object Browser, with the <globals> object selected in the list of classes, select the Cells property from the list of members.

   The description at the bottom of the Object Browser indicates that the Cells property returns a Range object.

   

2. In the Immediate window, type Cells.Select and press Enter.

   This selects all the cells on the worksheet. This is equivalent to clicking the box at the upper left corner of the worksheet, between the column A heading and the row 1 heading.

   

3. Type Cells.Item(5).Select and press Enter.

This selects cell E1, the fifth cell in the first row. The Cells property returns the range of all the cells on the worksheet as a collection. An individual item in the collection is a cell.

1. Type Cells.Item(16383).Select and press Enter.

   This selects cell XFC1, the next to the last cell in the first row. Excel 2007 now allows 16384 cells in a single row.

   

2. Type Cells.Item(16385).Select and press Enter.

   This selects cell A2, the first cell in the second row. When you use a single number to select an item in the Cells collection, the number wraps at the end of each row. Since each row of the worksheet contains 16384 cells, cell 16385 is the first cell on the second row.

3. Type Cells.Item(3,2).Select and press Enter.

   This selects cell B3, the third row and second column in the worksheet. Unlike most other collections, the Cells collection allows you to specify an item by using both the row and column values.

   

   On The CD-Important 

   In previous versions of Excel, the expression Cells.Item(257) referred to cell A2. In Excel 2007, it now refers to cell IW1, the 257th cell in the first row. In order to write macros that work in multiple versions, you should always use the row and column specification in the Cells function. Another consequence of the larger size of the worksheet is that you cannot use the expression Cells.Count to retrieve the number of cells on the worksheet, because the number is too big. This is unlikely to ever be a problem, but it illustrates the expanded size of the worksheet grid.

4. Type Cells.Item(1048576,16384).Select and press Enter.

   This selects cell XFD1048576, the bottom right cell in the worksheet. In case you wonder, these bizarre-looking numbers are really simple powers of 2. You could select the same cell by using the expression Cells.Item(2^20,2^14). You could also use the Range property-Range("XFD1048576").

   

5. Type Cells(1).Select and press Enter to select cell A1.

As with other collections, when you use the Cells property to get a collection of cells, you can leave out the Item method, and simply put the argument after the Cells property. The expression Cells(1) is equivalent to Cells.Item(1), which is equivalent to Range("A1"). All these expressions can be used interchangeably.

Refer to a Range as a Collection of Rows or Columns

In addition to referring to the worksheet range as a collection of cells, you can also think of it as a collection of rows or as a collection of columns. Analogous to the Cells property, the Rows property returns a collection of rows and the Columns property returns a collection of columns. These properties return collections, do not have their own object classes, and return Range objects.

1. In the Object Browser, with the <globals> object selected in the list of classes, select the Columns property in the list of Members.

   The description shows that this property, similar to the Range property and the Cells property, returns a Range object.

   

2. In the Immediate window, type Columns.Select and press Enter.

   This selects all the cells on the worksheet, exactly the same as Cells.Select. The difference between the two properties appears when you use the Item property to index into a single item in the collection.

3. Type Columns(3).Select and press Enter.

   This selects column C, the third column on the worksheet.

   

4. Type Columns("D").Select and press Enter.

   This selects column D. When you specify a column by letter, you are giving the name of the item and must enclose it in quotation marks.

   

5. Type Columns("B:H").Select and press Enter.

   This selects the range of columns from B through H. The only way to specify a range of columns within the collection is by using the column letter names.

   

6. Type Rows(2).Select and press Enter.

   This selects row 2. With rows, the name of an item is also a number. The expressions Rows(2) and Rows("2") are functionally equivalent.

   

7. Type Rows("3:14").Select and press Enter.

   This selects a range of rows. The only way to specify a range of rows within the collection is by using the row numbers as a name-that is, by enclosing them in quotation marks.

   

The <globals> object in the Object Browser includes three properties that return all the cells of a worksheet-Cells, Columns, and Rows. In each case, you get a reference to a Range object, but the properties return that object as a collection of cells, columns, or rows, respectively. There are no object classes for Cells, Columns, and Rows. These are simply different ways of representing the same Range object.

Refer to a Range Based on the Active Cell

Many times when writing a macro you want to refer to a range that is somehow related to the active cell or to the current selection. The macro recorder uses the Selection property to refer to the selected range and the ActiveCell property to refer to the one active cell. A Range object has useful properties that can extend the active cell or the selection to include particularly useful ranges.

1. In the Immediate window, type Range("B2").Select and press Enter.

   This selects the upper left cell of the sample list.

2. In the Object Browser, with the <globals> object selected in the Classes list, select the ActiveCell property.

   The description at the bottom of the Object Browser shows that this property returns a Range object.

3. In the Immediate window, click the Edit menu, and then click Complete Word. In the list of members, click ActiveCell.

   Tip 

   When you use the Complete Word command at the beginning of a statement- whether in a macro or in the Immediate window-the Auto List displays all the members of the <globals> object. If you like using the keyboard, you can press Ctrl+Space to display the list of members, type partial words and use arrow keys to select the desired member, and then press the Tab key to insert the member into the statement.

   

4. Type a period ( . ). Then type CurrentRegion.Select to create the statement ActiveCell.CurrentRegion.Select, and then press Enter.

   This selects the entire sample list. The CurrentRegion property selects a rectangular range that includes the original cell and is surrounded by either blank cells or the edge of the worksheet. It is hard to overstate the usefulness of the CurrentRegion property.

   

5. Type ActiveCell.EntireColumn.Select and press Enter.

   This selects all of column B because the active cell was cell B2. Because the starting range was the active cell-not the entire selection-the EntireColumn property returned a reference to only one column. Because the initial active cell-B2-is still within the selection, it is still the active cell.

   

6. In the Object Browser, with the <globals> object selected, select the Selection property in the list of members.

   The description at the bottom indicates that the Selection property returns an object, not a Range. The Selection property returns a Range object only when cells are selected. If shapes or parts of a chart are selected, this global property returns a different object type. Because the Selection object can return a different object type at different times, it does not display an Auto List the way the ActiveCell property does.

   

7. In the Immediate window, type Selection.CurrentRegion.Select and press Enter.

   This selects the range B1:H14-the entire sample list plus the one row above it. It’s acting the same as if the current selection were only cell B1. When you use the CurrentRegion property with a multicell range as the starting point, it ignores everything except the top-left cell of the range as it calculates the current region.

   

8. Type Range("A2").Activate and press Enter.

   Because the specified cell is outside of the current selection, the Activate method behaves the same as Select.

   

9. Type Selection.EntireRow.Select and press Enter.

   This selects all of row 2. Because the selection is a single cell, you would get exactly the same result by using ActiveCell.EntireRow.Select.

10. Type Range("B2").Activate and press Enter.

    Because the specified cell is within the selected range, this statement does not change the selection, but it does move the active cell to a new location within the range. If you activate a cell that is not within the current selection, the Activate method behaves the same as Select.

    

The Selection and ActiveCell properties are useful as starting points for deriving other ranges. The ActiveCell property always returns a reference to a Range object, and therefore displays a convenient Auto List when you are entering a statement. The Selection property returns a reference to a Range object only when a range is actually selected, and thus it does not display an Auto List.

Refer to Subsets of a Range

When you reference a range by using a property from the <globals> object-for example, Range, Cells, Columns, or Rows-you get a range based on the entire active worksheet. These same properties also exist as properties of a Range object. The easiest way to work with properties of a Range object is to declare a variable as a Range. Then the Auto List displays the methods and properties as you type a statement, even if you use the Selection property-which does not display Auto Lists-to assign the range to the variable.

1. In the Visual Basic editor, click Insert, and then click Module.

2. Type Sub TestRange and press Enter.

   Visual Basic adds parentheses and an End Sub statement.

3. Type Dim myRange As Range and press F8 twice to initialize the variable.

4. In the Immediate window, type Set myRange = Range("B2") and press Enter. Then type myRange.Select and press Enter again.

   This selects cell B2, confirming that the variable contains a reference to that cell.

5. Click the Object Browser button. In the list of classes, select the Range class. Then in the list of members, select the Range property.

   This Range property appears very similar to the Range property of the <globals> object. It behaves, however, relative to a starting range.

6. In the Immediate window, type myRange.Range("A1:G1").Select and press Enter.

   This does not select the range A1:G1. Rather, it selects the range B2:H2. If you think of cell B2 as the upper left cell of an imaginary worksheet, the range A1:G1 of that imaginary worksheet would correspond to the range B2:H2 of the real worksheet.

   

7. Type Set myRange = myRange.CurrentRegion and press Enter. Then type myRange.Select and press Enter again.

   Given that myRange already referred to cell B2, which is inside the sample list, the first statement references the entire sample list, and the second confirms that the variable contains a reference to the appropriate range.

8. Type myRange.Cells.Item(2,6).Select and press Enter.

   This selects the first data value in the Units column-row 2 and column 6 within the data region.

   

9. Type myRange.Rows(2).Select and press Enter.

   This selects the second row of values in the list, even though they exist in row 3 of the worksheet. A single row from the collection referenced by the global Rows property includes the entire row of the worksheet; the Rows property of a Range object includes only the cells within the range.

10. Type myRange.Rows(myRange.Rows.Count).Select and press Enter.

    This selects the last row of the list. Because the Rows property returns a collection, you can use the Count property to find the number of items in the collection. That count can then serve as an index into the same collection.

    

When you use the Range, Cells, Columns, or Rows properties as members of a Range object, the resulting ranges are relative to the upper-left cell of that range. Contrast this to when you use the same functions from the global group-or as members of the Application object or of a Worksheet object. With anything other than a Range object, these functions return ranges that are relative to the upper-left cell of the worksheet.

Refer to a Relative Range

Excel has other properties that can calculate a new range based on one or more existing ranges. Two of these properties do not exist in the list of global members; they exist only as members of a Range object: the Offset property references a range shifted down, up, left, or right from a starting range, and the Resize property references a range with a different number of rows or columns from a starting range. An additional property, the Intersect property, does appear in the list of global members. It is particularly valuable when you need to “trim away” part of a range, such as when you want to remove the header row from the current region.

1. In the Object Browser, select Range in the Classes list. Then, in the Members list, select the Offset property.

   The description indicates that this property has two arguments-RowOffset and ColumnOffset, both of which are optional-and that it returns a Range object.

   

2. In the Immediate window, type myRange.Offset(1).Select and press Enter.

   This selects a range identical in size and shape to the range stored in the variable, but shifted down by one cell. The first argument to the Offset property indicates the number of rows down to shift the range; the second argument indicates how many columns to the right to shift the range. Omitting an argument is the same as using zero and does not shift the range in that direction.

   

   Tip 

   To understand the Offset property, think of yourself as standing on the upper-left cell of the initial range. Face the bottom of the worksheet, and step forward the number of steps specified in the first argument. Zero steps means no movement. Negative steps are backwards. Then face the right side of the worksheet and do the same with the number of steps specified in the second argument. The resulting range is the same size and shape as the original one, but it begins on the cell you end up standing on.

3. In the Object Browser, select Range in the Classes list. Then, in the list of members, select the Resize property.

   The description indicates that this property has two arguments-RowSize and ColumnSize, both of which are optional-and that it returns a Range object.

4. In the Immediate window, type myRange.Offset(1).Resize(5).Select and press Enter.

   This selects the first five rows of data. The Offset property shifts the range down to omit the heading row. The Resize function changes the size of the resulting range. The first argument to the Resize property is the number of rows for the result range; the second is the number of columns for the result range. Omitting an argument is the same as keeping the size of the original range for that direction.

   

5. Type myRange.Offset(1,5).Resize(1,2).Select and press Enter.

   This selects the range G3:H3, which happens to be the numeric values in the first row of the body of the list.

   

   Tip 

   The combined functionality of the Offset and Resize properties is equivalent to that of the OFFSET function available on worksheets.

6. In the Object Browser, with the <globals> object selected in the list of classes, select the Intersect method in the Members list.

   The description shows that this method returns a Range object, but it also shows that it can take up to 30 arguments! In practice, you usually use two arguments, and you can see that the first two arguments are required. The Object Browser shows that the first two arguments must be range objects, but if you use more than two arguments, they do all need to be ranges. You can use the Intersect method in conjunction with the Offset method to remove headings from the current region.

   

7. In the Immediate window, type Intersect(myRange, myRange.Offset(1)).Select and press Enter.

   This selects the range B3:H14, which is the entire list except the heading row. You often need to manipulate the body of a list separately from the heading. By using a range as the first argument of the Intersect method, and then an offset version of the range as the second argument, you can trim off portions of the range.

   

8. Press F5 to end the macro.

The Offset and Resize properties, along with the EntireRow, EntireColumn, and CurrentRegion properties and the Intersect method, provide you with flexible tools for calculating new Range objects based on an original starting range. Often, the easiest way to work within a range is to first use the CurrentRegion property to establish the base range, and then use the Offset property and the Intersect method to manipulate the range.