Range Object and Cells Property

Share Now


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:

ActiveSheet.Range.Cells(1,1)
Range.Cells(1,1)
Cells(1,1)
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)



Share Now
March 4, 2012

0 responses on "Range Object and Cells Property"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

    Drop us a Query

    Call Us: +91 8826547882

    Drop us a Query