Cells Property

The Excel object model does not have an official Cells collection nor a Cell object. Nevertheless, the cells property acts as though it returns such a collection as a Range object. For instance, the following code returns 8 :

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

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

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

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.

0 0

Post a comment