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

Active Sheet.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 demonstrate how the Offset property can be implemented (assume the current cell before the move is E5):

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

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



Share Now
November 20, 2019

0 responses on "Range Object and Cells Property"

    Leave a Message

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

    Social Media

    facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting

    Address:

    NM 23, SECTOR 14, OLD DLF COLONY,
    GURGAON (HARYANA)
    0124- 4601426

    ABOUT EC ANALYTICS CONSULTING

    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED