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)
Search JabSto ::




Custom Search