What is the difference between Range.Item and Range.Cells?

Question

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’s description, they are defined as:

Range.Item Property (Excel)

Returns a Range object that represents a range when shifted to the specified range.

~

Range.Cells Property (Excel)

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

Notes

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

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

Answer 1

The best way to understand this is through the example below.

When .Item and .Cells are used with respect to a range, then YES, they are the 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 cell address in what Range

They differ when Cells() used regardless of 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 

The above .Item shows the cell address in Range , where as Cells represents the cell address in ActiveSheet

Answer 2

I do not see any use of the Cells property, except for a reference to all cells in the sheet. I would say 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. In the case of a worksheet, this is clearly useful in order to be able to use all the properties of a range object on the entire worksheet. In the case of a Range object, I'm not sure if it is being used. I cannot find, for example, any additional property that I cannot get from the properties of a range object without first applying the Cells property to this Range object

I am wondering if some of its use in code has simply crept in for years. For example, I believe that this type of code string is one of the possible. An explicit way to refer to a range object of a second cell on a sheet using the property of a range object object. Ws.Range ("A1"). Areas.Item (1) .Item (1, 2) According to what is said, I can rely on the implied implicit first property of Area and Default Item to rewrite this line of code this way: Ws.Range ("A1 " ) (12)

If I close the Cells property, I will not harm Ws.Range ("A1"). Cells (1, 2)

But I would suggest: _ (i) using the cells here is completely redundant _ (ii) I still use the range element property here. _ (iii) There is no property of the elements of the cell _ (iv) A code part similar to this cell (1, 2) “crept in” as the so-called “Cell”, which will take one or two arguments ........ ".. and / or ..." cells have the Item property ... "... etc. I think these statements may be incorrect. I think the Cells Property has no arguments. (I I’m not too sure that Cells has the Item property. I’m not a computer technician, but the experts told me that this suggestion is intellisense, or the Microsoft tip suggesting it does not guarantee that it exists. I expect not to oystva Cells Item)

In all cases, a piece of code like Cells (1, 2) is explained as follows: Cells return a range object. A Range object has its own default property, to which the property of a range object object is applied. If I don't use Cells regardless of Range, I probably should omit it. I suggest this because in this case I am not explicit. Rather, I contribute to the possibly false idea that I can refer to a Range object via _ .. _ .. "Cells (_argument / s_) of type" Property .., which may not exist.

Alan