Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the difference between Range.Item and Range.Cells?

Tags:

excel

vba

For example, in the code below, Item and Cells can be used interchangeably:

Dim rRange As Range
Set rRange = ThisWorkbook.ActiveSheet.Range("A1")

With rRange
    Debug.Print .Item(1, 1).Value   ' Outputs value of "A1"
    Debug.Print .Cells(1, 1).Value  ' Outputs value of "A1"

    Debug.Print .Item(2, 1).Value   ' Outputs value of "A2"
    Debug.Print .Cells(2, 1).Value  ' Outputs value of "A2"
End With

In the developer reference, they are defined as:

Range.Item Property (Excel)

Returns a Range object that represents a range at an offset to the specified range.

~

Range.Cells Property (Excel)

Returns a Range object that represents the cells in the specified range.

Remarks

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword.

From that remark, does it mean that Cells(1, 1) is actually a short for Cells.Item(1, 1)? Thus Cells(1, 1) is actually equivalent to Item(1, 1)? What am I missing?

like image 860
feelthhis Avatar asked Sep 16 '14 22:09

feelthhis


1 Answers

The best way to understand this is via the below example

When .Item and .Cells are used with respect to a range then, YES, they are same. For example

Sub Sample()
    Dim rRange As Range
    Set rRange = ThisWorkbook.ActiveSheet.Range("B1:C10")

    With rRange
        Debug.Print .Item(1, 3).Address '<~~ $D$1
        Debug.Print .Cells(1, 3).Address '<~~ $D$1
    End With
End Sub

In the above they both depict the address of the cell in that Range

They are different when Cells() is used independently of a range.

Sub Sample()
    Dim rRange As Range
    Set rRange = ThisWorkbook.ActiveSheet.Range("B1:C10")

    With rRange
        Debug.Print .Item(1, 3).Address  '<~~ $D$1
        '~~> DOT before Cells missing
        Debug.Print Cells(1, 3).Address  '<~~ $C$1
    End With
End Sub

In the above .Item depicts the address of the cell in that Range, where as Cells depicts the address of the cell in the ActiveSheet

like image 134
Siddharth Rout Avatar answered Oct 24 '22 07:10

Siddharth Rout