8. The Range Object

8.1 The Range Object as a "Utility" Object

Excel uses a "utility" object that does not represent an element of the program, but instead is used to interact with the spreadsheet: the Range object. In simplest terms, Range refers to a rectangular area on a worksheet, which can be a single cell, an entire row or group of rows, an entire column or group of columns, or all the cells on the worksheet.

You will need to become very familiar with the Range object since it is the object you will be using most often in macros. Many of the actions you take working in Excel are actually using the methods of the Range object, and accessing its properties. For example, activating a cell, entering text or a formula in a cell, sorting data, and find/replace are done by using the Range object. You will see this object very often in Excel macros.

8.2 Range Object Returned by an Object Property

Earlier in this tutorial we described how an object property can return an object, such as a collection object, instead of a value. Similarly, certain properties of the Worksheet object return a Range object. The properties Rows, Columns, and Cells do not return collection objects, since there is no Row, Column or Cell object (or collection of them). When we access those properties, a Range object is returned, which represents the entire row (or group of rows), column (or group of columns), or cell (or group of cells). ActiveCell returns a Range object that represents the cell that has focus.

The Range object has a property called Address, which identifies the area of the worksheet represented by that Range object. So, the expression Rows("5:5") returns a Range object whose address represents all of row 5 of the worksheet. The value of the Address property is always text, so it must be enclosed in quotation marks in VBA.

Rows("5:5") is equivalent to Rows(5). The former refers to all of row 5 by the range address; the latter refers to all of row 5 by, in effect, using the index as you would in a collection. Both expressions return a Range object referencing the entire fifth row. Columns("C:C") and Columns(3) work the same way. Rows, Columns, Cells with no address and no index return a range consisting of all of the rows, columns, or cells of the worksheet, respectively; e.g., Rows.Autofit "autofits" all the rows on the active worksheet.

The expression Range("C4") refers to cell C4, by returning a range object referencing that single cell. Range("C4").Activate makes that the active cell by calling the Activate method on Range object.

Unfortunately the detailed explanation of an expression like Range("C4") is somewhat confusing, but as long as you understand how to use it, it is not too important to understand why it works. This is how it is set out in VBA Help, so you should be at least be exposed to the details.

First, the Worksheet object has a property called Range. This Range property returns a Range object, with an address as specified by the Range property. Second, you will recall that unless qualified, the Range property is understood to refer to the active worksheet. So Range("C4") is equivalent to Application.ActiveWorkbook.ActiveWorksheet.Range("C4"), which refers to a property of the active worksheet, the Range property, which returns a Range object whose address is "C4". Thus you can access all the properties and method of the Range object, such as Activate.

8.3 Describing a Range

You can describe ranges in VBA in Excel similarly to ranges in an Excel formula, and in other ways as well. To describe a single cell, you can use the cell address: Range("A1") refers to that single cell. To describe a rectangular area, you can use the addresses of the upper-left and lower-right corners of the rectangle, e.g., Range("B3:G20"). In these examples, the expressions returns Range objects whose addresses are "A1" and "B3:G20", respectively.

You can also refer to a range by using row number and column number/column letter:

To refer to a range of one cell, you can used the form Cells(1, 4) or Cells(1, "D") to refer to Cell D1. This is equivalent to Range("D1").
To refer to a range consisting of multiple cells, you specify the upper-left cell and lower-right cell, both identified by row and column, e.g., Range(Cells(5, 2), Cells(8, "D")). This is equivalent to Range("B5:D8").
The technique of identifying ranges using Cells(row-number, column-number) is very useful because it allows identification of ranges using variables (covered later in this tutorial).

Pay attention to the parentheses; you need a closing parenthesis for every opening parenthesis in the expression. This is a common source of errors in macros. Also remember that if you use letters to identify columns, the letters must be enclosed in quotation marks.

To refer to entire rows, you can use the form Rows("3:3") for a single row; for a contiguous group of rows use Rows("4:6"). To refer to entire columns you can use the form Columns("4:4") or Columns("D:D") for a single column, Columns("4:8") or Columns("D:H") for a continguous group of columns.

If you are just referencing one row or column as the range, you could alternatively use the number of the row or column, e.g., Rows(2), Columns(1). But if you are referring to multiple rows or columns, you must use the address, e.g., Rows("3:9"), Columns("A:C"). A range address is text, so it must be enclosed in quotation marks. A row number or column numbers is a numerical value, so it is not enclosed in quotation marks.

8.4 The Most Important Members of the Range Object

You should become readily familiar with the following methods of the Range object:
Activate
Clear
Copy
Cut
Delete
Find
Insert
Select
Sort
You should become readily familiar with the following properties of the Range object:
Address
Cells
Column
Columns
ColumnWidth
End
Height
Hidden
HorizontalAlignment
Font
Formula
NumberFormat
Offset
Row
RowHeight
Rows
Value
VerticalAlignment
Width
WrapText
"Readily familiar" means you should be able to recognize and understand these members when they appear in macro code, and you should learn to use them (at first, by looking them up in VBA Help or on the internet) fairly fluently.

Many of the properties of the Range object are the same as properties of the WorkSheet object. This makes sense since the Range object represents a portion of the worksheet. The properties of the Range are a subset of the same properties of the worksheet, representing only those that are contained within the range. For example, in Range("B3:E5"), the first cell is B3, the second row is row 4. Thus the expression Range("B3:E5").Cells(1, 1) refers to cell B3 of the worksheet, and the expression Range("B3:E5").Rows(2) refers to row 4 of the worksheet. The expression Range("B3:E5").Rows.Count returns 3, the number of rows in the range.

8.5 The Selection Property in Excel

When you record a macro in Excel that includes performing an action, such as copy, on a selected range on a worksheet, you will see something like Selection.Copy in the code. This code is deceptive, since it looks like Selection is an object, as it is in Word. But in Excel Selection is a property; there is no Selection object in Excel. Like the properties Rows, Columns, and Cells, Selection in Excel actually returns a Range object, which refers to the area of the worksheet selected. Thus, the properties and methods available are those of the Range object. Do not look for help on the Selection object in Excel; it does not exist.