Difference between `Range`, `Range.Cells`, `Range.Rows`, etc., "subtypes"

Question

EDIT: Sub dump_range below shows that the address does not completely define a Range (as far as operating with its contents is concerned), something that I found surprising, and not clearly stated in MS documentation. There is something else, a "subtype". It appears that one cannot inquire about the "subtype", but only indirectly, via Count. The practical relevance of this point is that, if one defines a Sub (or Function) taking a Range as an argument, one should bear this in mind to code the Sub (something that I personally did not do) to avoid potential errors.

This question was sparked by this answer to Traversing `Cells` in a `Range`

In the code

Dim rng As Range, rng2 As Range, rng3 As Range
Set rng = Selection
Set rng2 = rng.Cells
Set rng3 = rng.Rows

(Question 1) what are the differences between objects rng, rng2, etc.?

I do not mean to get it explained only in words, but in terms of specification of what methods/properties and results are available for each, and make up the difference among the "subtypes". There should be some, as VBA works differently on them. But I do not find the specification. Actually, the Excel help for the Range.Cells Property is confusing to me: "Returns a Range object that represents the cells in the specified range." It looks to me that it should return the same object as the caller. Links to authoritative documentation will support an explanation.

One can see that there are some differences among the objects by using

Call dump_range(rng)   ' -> Range $A$1:$B$6, count = 12
Call dump_range(rng2)  ' -> Range $A$1:$B$6, count = 12
Call dump_range(rng3)  ' -> Range $A$1:$B$6, count = 6

with

Sub dump_range(ByRef rng As Range)
    Debug.Print "Range " & rng.Address & ", count = " & rng.Count
End Sub

(Question 2) I also want to know if one can test a Range object for which "subtype" it is. E.g., how would one discern the "subtypes" of objects rng and rng2, without inspecting the code? I.e., obtaining such information at run-time.

(Question 3) Is there a canonical word for "subtype"?

Answer 1

Question 1)

They are all the same class or object type (Range Object).

A range object is a collection of other “things” which can be cells, rows or columns etc. Each one of these is a range object.

For example, given a range R:

R.cells is a range object made up off all the cells (also range objects) i.e. {cell1, cell2, cell3, …}

R.rows is a range object made up of all the rows (also range objects) i.e. {row1, row2, row3, …} --> (similarly row1 is a range object is made up of all the cells i.e. {row1Cell1, row1Cell2, …})

And selection is equivalent to selection.cells, so in your example rng is equivalent to rng2.

Again,

Rows, cells, columns are all range objects, so they all have the same properties/methods. The reason you are getting a different count is not because they are different types of objects with different properties/methods. They are the same type of object, but not equivalent and thus some properties may be different.

To see a full list or props + methods, see here.

Question 2)

Subtype isn’t the correct word in this sense. Your examples are all the same type, there is no subtype. What they are is not equivalent. To test for “equivalence” (in the sense that you seek) you can loop through each object in the range object and recursively test equality (comparing the address strings) of every object until you get to a range object with a count of zero, see below:

 'should return "Rows"
 getRangeType(selection.Rows) 

 Function getRangeType(inputRange As Range) As String

      If (testRangeEquality(inputRange, inputRange.Rows)) Then
        getRangeType = "Rows"
      Exit Function
      End If

      If (testRangeEquality(inputRange, inputRange.columns)) Then
           getRangeType = "Columns"
           Exit Function
      End If

      If (testRangeEquality(inputRange, inputRange.Cells)) Then
          getRangeType = "Cells"
          Exit Function
      End If

 End Function


 Function testRangeLevelEquality(range1 As Range, range2 As Range) As Boolean

      If (range1.Count <> range2.Count) Then
          testRangeLevelEquality = False
          Exit Function
      End If

      IsEqual = True

      For i = 1 To range1.Count
           If (range1(i).Address <> range2(i).Address) Then
                IsEqual = False
           End If
      Next i

      testRangeLevelEquality = IsEqual

 End Function


 Function testRangeEquality(range1 As Range, range2 As Range) As Boolean

      Equality = True

      If (testRangeLevelEquality(range1, range2)) Then

           If (range1.Count = 1) Then

                Equality = True

           Else

               For i = 1 To range1.Count
                   If (testRangeEquality(range1(i), range2(i)) = False) Then
                       Equality = False
                       Exit For
                   End If
               Next i

           End If

      Else

           Equality = False

      End If

      testRangeEquality = Equality

 End Function

Question 3)

Again, there is not a notion of a subtype in this sense, just separate, nonequivalent instances of the same class. But, I think that inheritance may be the term you were looking for.

Answer 2

What is the difference between "Sancho" and "Sancho.Hand"?

---

Why am I taking this approach?

People have already tried to explain above but seems like you are not able to comprehend what they are saying, so I am going to use a different technique. :) This is basically what "Angel Eyes" has also said in his answer.

---

Ok the Explanation

If you look at it in one way then there is no difference. Both have bones, muscle, blood running through them, may have hair (unless you wax!)

On the other hand yes there is a difference. "Sancho" is a collection of all body parts like "Hand", "Nose", "Eyes" etc etc. So definitely a "Hand" <> "Complete Body"

Similarly Rng (Sancho) and Rng.Rows (Sancho.Hand) are same yet they are different!

So both will have .Value, .Address, .Count properties at their disposal. But the values these properties return may or may not be the same.

What you are trying to is an incorrect comparison. You can compare

.Address to .Address
.Count to .Count
.Value To .Value '<~~ Only possible with single cell range objects

rng and rng2 are range objects. There is NO difference when it comes to Type. They will have the same properties (Not values)

---

Looking for authoritative documentation?

You cannot find an authoritative document which tells What is the difference between "Sancho" and "Sancho.Hand", Er, I mean What is the difference between "rng" and "rng.Cells" Because it is pure common sense. What you may find is articles on what rng or rng.Cells is. From that you have to deduce what are the differences.

Answer 3

The difference is explained in Help:

Range.rows
Returns a Range object that represents the rows in the specified range. Read-only Range object

Compare
msgbox rng(1).address - returns A1 because its the first cell
msgbox rng3(1).address - returns A1:B1 because its the first row

if you want to find out what kind of range it is try this code

Sub TestRangeObj()
    Dim rng As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
    Set rng = Range("A1:B6")
    Set rng2 = rng.Resize(1, 1)
    Set rng3 = rng.Rows
    Set rng4 = rng.Columns
    Set rng5 = Union(rng, Range("x45:Z50"))
    MsgBox RangeType(rng)
    MsgBox RangeType(rng2)
    MsgBox RangeType(rng3)
    MsgBox RangeType(rng4)
    MsgBox RangeType(rng5)
End Sub
Function RangeType(theRange As Range) As String
Dim nRows As Long
Dim nCols As Long
nRows = theRange(1).Rows.Count
nCols = theRange(1).Columns.Count
    If theRange.Areas.Count > 1 Then
        RangeType = "Multi-Area"
    ElseIf theRange.CountLarge = 1 Then
        RangeType = "Cell"
    ElseIf nRows = 1 And nCols > 1 Then
        RangeType = "Row"
    ElseIf nRows > 1 And nCols = 1 Then
        RangeType = "Column"
    Else
        RangeType = "2-D Range"
    End If
End Function

Answer 4

Q3: no, there is none. When dealing with the Variant Data Type, which can contain (wrap) any Data Type, you can think of the variant's content as "subtype", e.g. "Variant containing Integer Data Type value"... Do read about / practice with VarType VBA function please.

Q2: this is meaningless (pls see below)

Q1:

let's say

Set rngX = Sheet1.Range("$A$1:$B$6")

rngX is a Range Object consisting of all the selected cell. rngX.Cells is also a Range Object consisting of all the same selected cell.

Cells property, as applied to a specified Range Object is superfluous for it returns the Range itself. The utility of the Cells property of the Range Object is realized when you do not specify the Range Object explicitly.

Let's say the active worksheet is Sheet1. Both calls below are equivalent and refer to a Range Object containing ALL THE CELLS in the worksheet Sheet1:

Sheet1.Cells
Cells

However, while the Sheet1 is active, you could refer to all the cells in some other worksheet like this:

Sheet2.Cells

That's all there is about that.

As for the Rows property of the Range Object, you are dealing with rows of cells not with individual cells. rngX.Rows returns a collection (an array) of Range Objects each of which is a single row in the rngX Range Object. In our example,

rngX.Rows

returns a collection of two Range Objects which are sub-ranges of the rngX, each representing cells in a row:

rngX.Rows(1)

is the Range Object Sheet1.Range("$A$1:$A$6"), and

rngX.Rows(2)

is the Range Object Sheet1.Range("$B$1:$B$6").

Please note that a row can consist of just one cell, as, for example, Range("A1:A100").Rows(3) is the same as Range("A3").

Aslo note that the Columns property of the Range Object works just like the Rows property, only 'vertically'.

Now, since these "sub-ranges" are Range Objects themselves, all that I've said above is applicable to them too, naturally.

Answer 5

Interesting question. I'd never noticed that the Count property of a range changes depending on how it was declared.

I don't think anybody has addressed the Range.Item property, which is helpful here. For many objects Item just represents an objects default property, e.g., ...

? ThisWorkbook.Worksheets.Item(1).Name = ThisWorkbook.Worksheets(1).Name

...returns True because it's just two ways of saying the same thing.

For a range it looks like Item actually references the collection of "subtypes" that you seek. In other words, if a range is defined using rows, as in your rng2 variable, the Item collection refers to rows.

Although I don't see a direct way to reference the type of the Item, it looks like you can pretty easily suss it out like this:

Function GetSubType(rng As Excel.Range) As String
Dim SubType As String
If rng.Item(1).Address = rng.Cells(1).Address Then
    SubType = "cell"
ElseIf rng.Item(1).Address = rng.Rows(1).Address Then
    SubType = "row"
ElseIf rng.Item(1).Address = rng.Columns(1).Address Then
    SubType = "column"
Else
    SubType = "who knows"
End If
GetSubType = SubType
End Function

Note that if you don't set a range equivalent to Rows, etc., but just to an address as in your rng variable, the "subtype" will be Cells.

In my brief testing this works on multi-area ranges.

Of course the practical answer to avoiding the potential errors you mention is to code explicitly. Instead of Range.Count, always use Range.Cells.Count, Range.Rows.Count, Range.Areas.Count, etc.

Finally, why does MS call it Item, instead of Items, like any other collection?

Answer 6

To answer your questions, There is a way to check the type of a variable at run-time, using the VarType function.

However, in your case:

Dim rng As Range
Set rng = Range("A1:B6")
MsgBox TypeName(rng) & " : " & VarType(rng) & " : " & rng.Count' Range : 8204 : 12
MsgBox TypeName(rng.Rows) & " : " & VarType(rng.Rows) & " : " & rng.Rows.Count' Range : 8204 : 6
MsgBox TypeName(rng.Columns) & " : " & VarType(rng.Columns) & " : " & rng.Columns.Count' Range : 8204 : 2
MsgBox TypeName(rng.Cells) & " : " & VarType(rng.Cells) & " : " & rng.Cells.Count ' Range : 8204 : 12

As you can see, Excel implements all three variable as the same type.

Therefore, I deduce that there is an private state to the Range class that determines whether it is in the context of Cells,Rows or Columns, with Cells being the default

Unfortunately, non of the properties of the Range class expose this kind of state

Answer 7

The short answer is that all three ranges are the same. You would never use Range.Cells except to refer to a cell within that range since .Cells returns all cells in that range. And for Range.Rows it is the same. A row in Excel is simply the collection of cells in the row of the range.