Microsoft Office Tutorials and References

In Depth Information

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

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 advantage 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

Dim rng As Range

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.

19.2.3 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)