Range() vs Cells() vs Range.Cells()

Question

I know that the Range() and Cells() properties are equivalent methods of accessing cells on a worksheet. However, when is it wise to use Range.Cells() in this combination?

I came across an example where they used Range("A1").Resize(2,3).cells.value. Would this be equivalent to Range("A1").Resize(2,3).value?

If not, what is the advantage of the former?

Answer 1

Technically, Range and Range.Cells are not equivalent. There is a small but important difference.

However in your particular case, where you

1. Construct the range with Range("something"), and
2. Are only interested in the .Value of that range,

it makes no difference at all.

---

There is a handy clause in VB, For Each, that enumerates all elements in a collection. In the Excel object model, there are convenient properties such as Columns, Rows, or Cells, that return collections of respective cell spans: a collection of columns, a collection of rows, or a collection of cells.

From how the language flows, you would naturally expect that For Each c In SomeRange.Columns would enumerate columns, one at a time, and that For Each r In SomeRange.Rows would enumerate rows, one at a time. And indeed they do just that.
But you can notice that the Columns property returns a Range, and the Rows property also returns a Range. Yet, the former Range would tell the For Each that it's a "collection of columns", and the latter Range would introduce itself as a "collection of rows".

This works because apparently there is a hidden flag inside each instance of the Range class, that decides how this instance of Range will behave inside a For Each.

Querying Cells off a Range makes sure that you get an instance of Range that has the For Each enumeration mode set to "cells". If you are not going to For Each the range to begin with, that difference makes no difference to you.

And even if you did care about the For Each mode, in your particular case Range("A1").Resize(2,3) and Range("A1").Resize(2,3).Cells are the same too, because by default the Range is constructed with enumeration mode of "cells", and Resize does not change the enumeration mode of the range it resizes.

---

So the only case that I can think of where querying Cells from an already existing Range would make a difference, is when you have a function that accepts a Range as a parameter, you don't know how that Range was constructed, you want to enumerate individual cells in that range, and you want to be sure that it's cells For Each is going to enumerate, not rows or columns:

function DoSomething(byval r as range)
  dim c as range

  'for each c in r ' Wrong - we don't know what we are going to enumerate

  for each c in r.cells ' Make sure we enumerate cells and not rows or columns (or cells sometimes)
    ...
  next
end function

Answer 2

Both

Dim b As Variant
b = Range("A1").Resize(2, 3).Cells.Value

and

Dim c As Variant
c = Range("A1").Resize(2, 3).Value

will return the same array of values. So they are equivalent. No advantage which one you use (one is shorter).

---

But you can use

Range("A1").Resize(2, 3).Cells(1, 2).Value

to get a specific cell out of that range. So this is the most likely where you need the .Cells on .Range. Note that the row/column numbers in Cells(1, 2) are relative to the range not the absolute numbers of the worksheet.

So the differences are:

Range("A1:A2")              'range can return multiple cells …
Range("A1")                 '… or one cell.
Cells(1, 2)                 'cells can return one cell or …
Cells                       '… all cells of the sheet

Range("A1:A2").Cells        'returns all cells of that range and therefore is the same as …
Range("A1:A2")              '… which also returns all cells of that range.

Range("C5:C10").Cells(2, 1) 'returns the second row cell of that range which is C6, but …
Cells(2, 1)                 'returns the second row cell of the sheet which is A2