CODEWITHSUNDEEP
excelvba
feelthhis
feelthhis

Reputation: 357

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

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?

Upvotes: 9

Views: 9011

Answers (2)

Alan Elston
Alan Elston

Reputation: 99

I can see no use of the Cells Property other than for referring to all the cells in a Worksheet. I would put it like this, ( based on my experiments and research) :

The Cells Property returns a Range Object containing all the cells of the Object to which it is applied. For the case of a Worksheet it is clearly useful so as to be able to use all the Properties of the Range Object on the entire Worksheet. For the case of a Range Object I am not sure if it has any use. I cannot find, for example, any extra Property that I cannot get from the Range Object's Properties without first applying the Cells Property to that Range Object

I am wondering if some of its use in code has just “crept in” over the years. For example , I believe this type of code line is one possible Explicit way to refer to the Range Object of the second cell in a worksheet using the Range Object Item Property Ws.Range("A1").Areas.Item(1).Item(1, 2) Along the lines of what feelthhis said, I can rely on the implied implicit of first Area and Default Property of Item to rewrite that code line thus: Ws.Range("A1")(1, 2)

If I drop in a Cells Property , I do no harm Ws.Range("A1").Cells(1, 2)

But I would suggest: _(i) use of Cells here is totally redundant _(ii) I am still using here the Range Item Property. _(iii) There is no Cells Item Property _(iv) A code part like this Cells(1, 2) has “crept in” as a so called “Cells Property that will accept one or two arguments........”..and/ or ...”cells has a Item Property...”...etc... ,. I think those statements may be incorrect. I think the Cells Property has no argument. ( I am not too sure if Cells may have an Item Property. I am not a computing professional but experts have told me that intellisense offering it , or Microsoft Help suggesting it is no guarantee that it exists. I expect there is no Cells Item Property)

In all case a code part like this, Cells(1, 2), is explained as follows: Cells is returning a Range Object. That Range Object is having its default Property, the Range Object Item Property applied to it. Unless I am using Cells independently to a Range, I should probably omit it. I suggest this as, in this case, I am not being explicit. Rather I am contributing to a, possibly false, idea that I can refer to a Range Object via a _.. _.. “Cells(_argument/s_) type” Property, .. which possibly does not exist.

Alan

Upvotes: -1

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

Upvotes: 6

Related Questions