11

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?

Community
  • 1
  • 1
rajomato
  • 1,167
  • 2
  • 10
  • 25
  • Range.Resize is for altering the height and width of the row and column, so all of your examples using resize(2,3).value return errors. So, I guess they are equivalent in the sense that they don't work. :-) ... Whether you use just `range` or `cells` or any of the other methods of referencing cells depends mostly on the size of the range and what you are trying to do, whether you will be looping though cells or acting on all at once, etc. If you can post an example of what you are trying to do, I can probably give you some case-specific help. – SeanW333 Jun 29 '18 at 03:48
  • 2
    @SeanW333 that's wrong. `resize(2,3).value` will not return an error. It will just return a valid array of values of that range. – Pᴇʜ Jun 29 '18 at 07:03
  • @Pᴇʜ I get an "Invalid use of property error" when I run either `Range("A1").Resize(2,3).value` or `Range("A1").Resize(2,3).cells.value`. So, at least on my system, it would seem I'm not wrong. – SeanW333 Jun 29 '18 at 07:12
  • 2
    @SeanW333 Well, `Dim c: c = Range("A1").Resize(2, 3).Cells.Value` should return an array in `c`. Also see my answer below. This definitely works when used correctly. – Pᴇʜ Jun 29 '18 at 07:20
  • Okay, good to know. I tested using exactly what the op posted, since there was no additional context provided, and both lines caused errors. I'm glad you came along and were able to correctly guess what they wanted to do. – SeanW333 Jun 29 '18 at 08:39
  • @SeanW333 well more to the point your issue was that "Range.Resize is for altering the height and width of the row and column" is wrong. Range.Resize changes the shape of the range. – Alex M Sep 20 '19 at 19:13

2 Answers2

15

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
GSerg
  • 76,472
  • 17
  • 159
  • 346
6

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).

enter image description here

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73