What's the difference between Range.Item and Range.Cells?
The best way to understand this is via the below example
.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 WithEnd Sub
In the above they both depict the address of the cell in that
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 WithEnd 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
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 PropertyWs.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 harmWs.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.