Empty Excel range collection possible?

Question

Is it possible to have an empty Excel range collection, i.e. can it happen to have an object obj of type Excel.Range such that

obj.Cells.Count = 0

I have the strong feeling that this is not possible.

Please give an authoritative references that it is indeed impossible, or provide a counter example.

score 1 · Accepted Answer · edited May 23 '17 at 11:52

1

I think this will partially answer your question.
All I can say on top of that is that all objects have to be Set in order to call any type of methods on them. It's the basics of the OOP concept. You can have an object of any type for example
Dim obj as Range
Dim obj as Application
Dim obj as Long
but it really is just a reserved room in the memory for this variable and is waiting to have a reference assigned.
So simply the answer is: No, it is not possible to have an empty range object set.
Check it out yourself:

Sub RangeTest()
    Dim rng As Range
    'Set rng = Range("A1")
    ActiveWorkbook.Names.Add Name:="rngName", RefersTo:=rng.Address
    MsgBox "count: " & rng.Cells.Count
End Sub

obviously the above fails, however »

Sub RangeTest()
    Dim rng As Range
    Set rng = Range("A1")
    ActiveWorkbook.Names.Add Name:="rngName", RefersTo:=rng.Address
    MsgBox "count: " & rng.Cells.Count
End Sub

also Developer’s Guide to the Excel 2010 Range Object clearly states

The Range object represents one or more cells, and can be used to represent a single cell, a row, a column, a selection of cells that contain one or more contiguous blocks of cells, or a 3-D range.

edited May 23 '17 at 11:52

Community

1
1

answered Jul 05 '13 at 08:55

The question asks for an object of type Range, as such the variable must be assigned, not just declared. However, the reference you supplied in the last part answers the question. – Wolfgang Kuehn Jul 05 '13 at 09:43
3

You can end up with an invalid `Range` by deleting cells - see [this question](http://stackoverflow.com/questions/12127311/vba-what-happens-to-range-objects-if-user-deletes-cells) – barrowc Jul 05 '13 at 11:43
@barrowc thats a great link! – Jul 05 '13 at 11:48
The problem behind was to have a User Defined Function returning a range collection. For example 'SUM(MyUDF(SOME_FILTER))'. Now if the filter does not apply to any cell, what to return if not an empty collection? Returning an invalid range complicates things further down the line, in this case the 'SUM' will be invalid. – Wolfgang Kuehn Jul 06 '13 at 10:20

score 0 · Answer 2 · answered Oct 29 '20 at 15:23

The correct representation of an empty range is Nothing. For example:

Debug.Print Intersect(Range("A1"), Range("B1")) Is Nothing
True

So your function should return Nothing. While there is such a thing as an empty collection, a Range cannot be an empty collection.

Empty Excel range collection possible?

2 Answers2