Range Object and Cells Property

Range Object and Cells Property

Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.  We will show you some examples on how Range object can be used.

The following example places text “AB” in range A1:B5, on Sheet2.

Worksheets(“Sheet2”).Range(“A1:B5”) = “AB”

Note that, Worksheets.Range(“A1”, “B5”) = “AB” will yield the same result as the above example.

The following place “AAA” on cell A1, A3, and A5 on Sheet2.

Worksheets(“Sheet2”).Range(“A1, A3, A5”) = “AAA”

Range object has a Cells property.  This property is used in every VBA projects on this website (very important).  The Cells property takes one or two indexes as its parameters.
For example,

Cells(index) or Cells(row, column)
where row is the row index and column is the column index.

The following three statements are interchangable:

The following returns the same outcome:

Range(“A1”) = 123    and    Cells(1,1) = 123
The following puts “XYZ” on Cells(1,12) or Range(“L1”) assume cell A1 is the current cell:

Cells(12) = “XYZ”
The following puts “XYZ” on cell C3:

Range(“B1:F5”).cells(12) = “ZYZ”

* The small gray number on each of the cells is just for reference purpose only. They are used to show how the cells are indexed within the range.

Here is a sub routine that prints the corresponding row and column index from A1 to E5.

Sub CellsExample()
For i = 1 To 5
For j = 1 To 5
Cells(i, j) = “Row ” & i & ”   Col ” & j
Next j
Next i
End Sub

Range object has an Offset property that can be very handy when one wants to move the active cell around.  The following examples demostrate how the Offset property can be implemented (assume the current cell before the move is E5):

ActiveCell.Offset(1,0) = 1                       Place a “1” one row under E5 (on E6)

ActiveCell.Offset(0,1) = 1                       Place a “1” one column to the right of E5 (on F5)

March 4, 2012

0 Responses on Range Object and Cells Property"

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com