**Excerpt from Chapter 19 of Writing Excel Macros by Steven Roman, published by O'Reilly and Associates. ISBN 1-56592-587-4**

**Copyright © 1999 by The Roman Press, Inc. All Rights Reserved. You may view and print this document for your own personal use only. No portion of this document may be sold or incorporated into any other document for any reason.**

The *Range* object is one of the workhorse objects in the Excel object model. Simply put, in order to work with a portion of an Excel worksheet, we generally need to first identify that portion as a *Range* object. As Microsoft puts it, a *Range* object "Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range."

Table 1 shows the 158 members of the *Range* object.

Table 1 - Members of the Range Object | ||

Items marked with an asterisk (*) are discussed in the text | ||

Activate* |
EntireColumn* |
Parse* |

AddComment* |
EntireRow* |
PasteSpecial* |

AddIndent |
FillDown* |
Phonetic |

Address* |
FillLeft* |
PivotField |

AddressLocal |
FillRight* |
PivotItem |

AdvancedFilter |
FillUp* |
PivotTable |

Application |
Find* |
Precedents* |

ApplyNames |
FindNext* |
PrefixCharacter |

ApplyOutlineStyles |
FindPrevious* |
Previous* |

Areas |
Font |
PrintOut* |

AutoComplete |
FormatConditions |
PrintPreview* |

AutoFill* |
Formula* |
QueryTable |

AutoFilter |
FormulaArray* |
Range* |

AutoFit* |
FormulaHidden* |
ReadingOrder |

AutoFormat* |
FormulaLabel |
RemoveSubtotal |

AutoOutline |
FormulaLocal |
Replace* |

BorderAround* |
FormulaR1C1* |
Resize |

Borders |
FormulaR1C1Local |
Row* |

Calculate* |
FunctionWizard |
RowDifferences* |

Cells* |
GoalSeek |
RowHeight* |

Characters |
Group |
Rows* |

CheckSpelling |
HasArray |
Run |

Clear* |
HasFormula* |
Select* |

ClearComments |
Height* |
Show |

ClearContents* |
Hidden |
ShowDependents |

ClearFormats* |
HorizontalAlignment* |
ShowDetail |

ClearNotes |
Hyperlinks |
ShowErrors |

ClearOutline |
IndentLevel* |
ShowPrecedents |

Column* |
Insert* |
ShrinkToFit* |

ColumnDifferences* |
InsertIndent* |
Sort* |

Columns* |
Interior |
SortSpecial |

ColumnWidth* |
Item |
SoundNote |

Comment |
Justify |
SpecialCells* |

Consolidate* |
Left* |
Style |

Copy* |
ListHeaderRows |
SubscribeTo |

CopyFromRecordset* |
ListNames |
Subtotal |

CopyPicture |
LocationInTable |
Summary |

Count |
Locked* |
Table |

CreateNames* |
Merge* |
Text |

CreatePublisher |
MergeArea* |
TextToColumns* |

Creator |
MergeCells* |
Top* |

CurrentArray |
Name |
Ungroup |

CurrentRegion* |
NavigateArrow |
UnMerge* |

Cut* |
NewEnum |
UseStandardHeight |

DataSeries |
Next* |
UseStandardWidth |

Default |
NoteText |
Validation |

Delete* |
NumberFormat* |
Value* |

Dependents* |
NumberFormatLocal |
Value2 |

DialogBox |
Offset* |
VerticalAlignment |

DirectDependents* |
Orientation |
Width* |

DirectPrecedents* |
OutlineLevel |
Worksheet |

EditionOptions |
PageBreak |
WrapText* |

End* |
Parent |

Our plan in this chapter is to first explore ways of defining *Range* objects. Then we will discuss many of the properties and methods of this object, as indicated in Table 1. As we have mentioned, our goal is not to cover all aspects of the Excel object model, but to cover the main portions of the model and to provide you with a sufficient foundation so that you can pick up whatever else you may need by using the help system.

The *Range* object is rather unusual in that it often acts like a collection object as well as a noncollection object. For instance, it has an *Item* method and a *Count* property. On the other hand, the *Range* object has many more noncollection-type members than is typical of collection objects. In particular, the average member count among all other collection objects is 19, whereas the *Range* object has 158 members.

The *Range* object should be thought of as a collection object that can hold other *Range* objects. To illustrate, consider the following code:

Dim rng as Range

Set rng = Range("A1", "C5").Cells

MsgBox rng.Count ' displays 15

Set rng = Range("A1", "C5").Rows

MsgBox rng.Count ' displays 5

Set rng = Range("A1", "C5").Columns

MsgBox rng.Count ' displays 3

In this code, we alternately set rng to the collection of all cells, rows and columns of the range "A1:C5". In each case, the *MsgBox* reports the correct number of items in the collection. Note that the Excel model does not have a cell, row or column object. Rather, these objects are *Range* objects, that is, the members of rng are *Range* objects.

When we do not specify the member type, a *Range* object acts like a collection of cells. To illustrate, observe that the code

Set rng = Range("A1", "C5")

MsgBox rng.Count

MsgBox rng(6).Value ' row-major order

displays the number of cells in the range and then the value of cell 6 in that range (counted in row-major order; that is, starting with the first row and counting from left to right). Also, the code

Dim rng As Range

Dim oCell As Range

Set rng = Range("A1", "C5")

For Each oCell In rng

Debug.Print oCell.Value

Next

will cycle through each cell in the range rng, printing cell values.

As witness to the importance of the *Range* object, there are a total of 113 members (properties and methods) throughout the Excel object model that return a *Range* object. This number drops to 51 if we count only *distinct* member names, as shown in Table 2. (For instance, *BottomRightCell* is a property of 21 differenct objects, as is *TopRightCell*.)

Table 2 - Excel Members that return a Range object | ||

ActiveCell |
DirectPrecedents |
Previous |

BottomRightCell |
End |
Range |

Cells |
EntireColumn |
RangeSelection |

ChangingCells |
EntireRow |
RefersToRange |

CircularReference |
Find |
Resize |

ColumnDifferences |
FindNext |
ResultRange |

ColumnRange |
FindPrevious |
RowDifferences |

Columns |
Intersect |
RowRange |

CurrentArray |
Item |
Rows |

CurrentRegion |
LabelRange |
SourceRange |

DataBodyRange |
Location |
SpecialCells |

DataLabelRange |
MergeArea |
TableRange1 |

DataRange |
Next |
TableRange2 |

Default |
Offset |
TopLeftCell |

Dependents |
PageRange |
Union |

Destination |
PageRangeCells |
UsedRange |

DirectDependents |
Precedents |
VisibleRange |

Let us take a look at some of the more prominant ways to define a *Range* object.

**Range Property**

The *Range* property applies to the *Application*, *Range* and *Worksheet* objects. Note that

Application.Range

is equivalent to

ActiveSheet.Range

When *Range* is used without qualification within the code module of a worksheet, then it is applied to that sheet. When *Range* is used without qualification in a code module for a workbook, then it applies to the active worksheet in that workbook.

Thus, for example, if the following code appears in the code module for Sheet2

Worksheets(1).Activate

Range("D1").Value = "test"

then its execution first activates Sheet1, but still places the word *test* in cell D1 of *Sheet2*. Because this makes code difficult to read, I suggest that you always qualify use of the *Range* property.

The *Range* property has two distinct syntaxes. The first syntax is

object.Range(Name)

where *Name* is the name of the range. It must be an A1-style reference and can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). Any dollar signs in the *Name* are ignored. We can also use the name of a named range. To illustrate, here are some examples

Range("A2")

Range("A2:B3")

Range("A2:F3 A1:D5") ' An intersection

Range("A2:F3, A1:D5") ' A union

Of course, we can use the *ConvertFormula* method to convert a formula from R1C1 style to A1 style before applying the *Range* property, as in

Range(Application.ConvertFormula( _

"R2C5:R6C9", xlR1C1, xlA1))

Finally, if *TestRange* is the *name* of a range, then we may write

Range(Application.Names("TestRange"))

or

Range(Application.Names!TestRange)

to return this range.

The second syntax for the *Range* property is

object.Range(Cell1, Cell2)

Here *Cell1* is the cell in the upper left corner of the range and *Cell2* is the cell in the lower-right corner, as in

Range("D4", "F8")

Alternatively, *Cell1* and *Cell2* can be *Range* objects that represent a row or column. For instance, the following returns the *Range* object that represents the second and third rows of the active sheet

Range(Rows(2), Rows(3))

It is important to note that when the *Range* property is applied to a *Range* object, all references are relative to the upper left corner cell in that range. For instance, if rng represents the second column in the active sheet, then

rng.Range("A2")

is the second cell in that column, and not cell A2 of the worksheet. Also, the expression

rng.Range("B2")

represents the (absolute) cell C2, because this cell is in the second column and second row from cell B1 (which is the upper left cell in the range rng).

**Cells Property**

The Excel object model does not have an official *Cells* collection nor a *Cell* object. Nevertheless, the *Cells* property acts as though is was returning such a collection as a *Range* object. For instance, the code

Range("A1:B4").Cells.Count

returns 8. (Incidentally, Cells.Count returns 16,777,216 = 256 * 65536.)

The *Cells* property applies to the *Application*, *Range* and *Worksheet* objects (and is global). When applied to the *Worksheet* object, it returns the *Range* object that represents all of the cells on the worksheet. Moreover, the following are equivalent:

Cells

Application.Cells

ActiveSheet.Cells

When applied to a *Range* object, the *Cells* property simply returns the same object, and hence does nothing.

The syntax

Cells(i,j)

returns the *Range* object representing the cell at row i and column j. Thus, for instance,

Cells(1,1)

is equivalent to

Range("A1")

One advantange of the *Cells* property over the *Range* method is that the *Cells* property can accept integer variables. For instance, the following code searches the first 100 rows of column 4 for the first cell containing the word *test*. If such a cell is found, it is selected. If not, a message is displayed.

Dim r As Long

For r = 1 To 100

If Cells(r, 4).Value = "test" Then

Cells(r, 4).Select

Exit For

End If

Next

If r = 101 then MsgBox "No such cell."

It is also possible to combine the *Range* and *Cells* properties in a useful way. For example, consider the following code

Dim r As Long

With ActiveSheet

For r = 1 To 100

If Cells(r, r).Value <> "" Then

Set rng = .Range(.Cells(1, 1), .Cells(r, r))

Exit For

End If

Next

End With

rng.Select

This code searches the diagonal cells (cells with the same row and column number) until it finds a nonempty cell. It then sets rng to refer to the range consisting of the rectangle whose upper-left corner is cell A1 and whose lower-right corner is the cell found in this search.

**Column, Columns, Row and Rows Properties**

The Excel object model does not have an official *Columns* or *Rows* collection. However, the *Columns* property does return a collection of *Range* objects, each of which represents a column. Thus,

ActiveSheet.Columns(i)

is the *Range* object that refers to the i-th column of the active worksheet (and is a collection of the cells in that column). Similarly,

ActiveSheet.Rows(i)

refers to the i-th row of the active worksheet.

The *Columns* and *Rows* properties can also be used with a *Range* object. Perhaps the simplest way to think of

rng.Columns

is as the collection of all columns in the worksheet *reindexed* so that column 1 is the leftmost column that intersects the range rng. To support this statement, consider the following code, whose results are shown in Figure 1:

Dim i As Integer

Dim rng As Range

Set rng = Range("D1:E1, G1:I1")

rng.Select

MsgBox "First column in range is " & rng.Column ' Displays 4

MsgBox "Column count is " & rng.Columns.Count ' Displays 2

For i = -(rng.Column - 2) To rng.Columns.Count + 1

rng.Columns(i).Cells(1, 1).Value = i

Next

Figure 1

Note that the range rng is selected in Figure 1 (and includes cell D1). Now, the *Column* property of a *Range* object returns the leftmost column that intersects the range. (Similarly, the *Row* property returns the topmost row that intersects the range.) Hence, the first message box will display the number 4.

Now, from the point of view of rng, *Columns(1)* is column number 4 of the worksheet (column D). Hence, *Columns(0)* is column number 3 of the worksheet (column C) which, incidentally, is not part of rng. Indeed, the first column of the worksheet is column number

-(rng.Column - 2)

of the worksheet, which is precisely why we started the For loop at this value.

Next, observe that

rng.Columns.Count

is equal to 2 (which is the number displayed by the second message box). This is because the *Count* property of rng.Columns returns the number of columns that intersect only the *leftmost area* in the range, which is area D1:E1. (We will discuss areas a bit later.) Finally, note that

rng.Columns(3)

is column F, which does not intersect the range at all.

As another illustration, consider the range selected in Figure 2. This range is the union B4:C5, E2:E7.

Figure 2

The code

Dim rng As Range

Set rng = Range("B4:C5, E2:E7")

MsgBox rng.Columns(1).Cells(1, 1).Value

displays an "x" as shown in the figure (in cell B4) because the indices in the *Cells* property are taken relative to the upper cell in the leftmost area in the range.

Note that we can use either integers or characters (in quotes) to denote a column, as in

Columns(5)

and

Columns("E")

We can also write, for instance

Columns("A:D")

to denote columns A through D. Similarly, we can denote multiple rows as in

Rows("1:3")

Since a syntax such as

Columns("C:D", "G:H")

does not work, the *Union* method is often useful in connection with the *Columns* and *Rows* methods. For instance, the code

Dim rng As Range

Set rng = Union(Rows(3), Rows(5), Rows(7))

rng.Select

selects the third, fifth and seventh rows of the worksheet containing this code or of the active worksheet if this code is in a workbook or standard code module.

**Offset Property**

The *Offset* property is used to return a range that is offset from a given range by a certain number of rows and/or columns. The syntax is

RangeObject.Offset(RowOffset, ColumnOffset)

where the *RowOffset* is the number of rows by which the range is to be offset and the *ColumnOffset* is the number of columns. Note that both of these parameters are optional, with default value 0 and both can be either positive, negative or 0.

For instance, the following code searches the first 100 cells to the immediate right of cell D2 for an empty cell:

Dim rng As Range

Dim i As Integer

Set rng = Range("D2")

For i = 1 To 100

If rng.Offset(0, i).Value = "" Then

MsgBox "Found empty cell at offset " & i & " from cell D2"

End If

Next