In VBA the Rows property has a weird behavior

Question

I am trying to figure out how to work on a specific row among a big range. However it appears that a range created with the rows property does not behave the same as a simple range. Check the following code, the first time the variable SpecificRow is defined, it is not possible to select a specific cell. However with a weird workaround that redefines the range, it works fine. Do you have an idea why and how could I define the range with a more elegant way?

'The following shows the weird behavior of Rows property
Dim SourceRng As Range
Dim SpecificRow As Range
Dim i As Long

i = 3

Set SourceRng = Range("A1:D20")
Set SpecificRow = SourceRng.Rows(i)

'This will show the address of the selected row ("A3:D3")
MsgBox SpecificRow.Address

'Unexplicable behavior of the range when trying to select a specific cell
'where it will instead consider the whole row (within the limits of SourceRng)
MsgBox SpecificRow(1).Address
MsgBox SpecificRow(2).Address

'This would send an error
'MsgBox SpecificRow(1, 1).Address

'Workaround
Set SpecificRow = Intersect(SpecificRow, SpecificRow)

'The following will select the same address than before
MsgBox SpecificRow.Address

'However, now it has a correct behavior when selecting a specific cell
MsgBox SpecificRow(1).Address
MsgBox SpecificRow(2).Address

Answer 1

You should expect weird behavior if you're passing indexed properties the incorrect parameters. As demonstrated by your code, the Range returned by SourceRng.Rows(i) is actually correct. It just isn't doing what you think it's doing. The Rows property of a Range just returns a pointer to the exact same Range object that it was called on. You can see that in its typelib definition:

HRESULT _stdcall Rows([out, retval] Range** RHS);

Note that it doesn't take any parameters. The returned Range object is what you're providing the indexing for, and you're indexing it based on it's default property of Item (technically it's _Default, but the 2 are interchangeable). The first parameter (which is the only one you're passing with Rows(i), is RowIndex. So Rows(i) is exactly the same thing as Rows.Item(RowIndex:=i). You can actually see this in the IntelliSense tooltip that pops up when you provide a Row index:

Excel handles the indexing differently on this call though, because providing any value parameter for the second parameter is a Run-time error '1004'. Note that a similar property call is going on when you call SpecificRow(1).Address. Again, the default property of Range is Range.Item(), so you're specifying a row again - not a column. SpecificRow(1).Address is exactly the same thing as SpecificRow.Item(RowIndex:=1).Address.

The oddity in Excel appears to be that the Range returned by Range.Rows "forgets" the fact that it was called within the context of a Rows call and doesn't suppress the column indexer anymore. Remember from the typelib definition above that the object returned is just a pointer back to the original Range object. That means SpecificRow(2) "leaks" out of the narrowed context.

All things considered, I'd say the Excel Rows implementation is somewhat of a hack. Application.Intersect(SpecificRow, SpecificRow) is apparently giving you back a new "hard" Range object, but the last 2 lines of code are not what you should consider "correct" behavior. Again, when you provide only the first parameter to Range.Items, it is declared as the RowIndex:

What appears to happen is that Excel determines that there is only one row in the Range at this point and just assumes that the single parameter passed is a ColumnIndex.

As pointed out by @CallumDA, you can avoid all of this squirrelly behavior by not relying on default properties at all and explicitly providing all of the indexes that you need, i.e.:

Debug.Print SpecificRow.Item(1, 1).Address
'...or...
Debug.Print SpecificRow.Cells(1, 1).Address

Answer 2

This is how I would work with rows and specific cells within those rows. The only real difference is the use of .Cells():

Sub WorkingWithRows()
    Dim rng As Range, rngRow As Range
    Set rng = Sheet1.Range("A1:C3")

    For Each rngRow In rng.Rows
        Debug.Print rngRow.Cells(1, 1).Address
        Debug.Print rngRow.Cells(1, 2).Address
        Debug.Print rngRow.Cells(1, 3).Address
    Next rngRow
End Sub

which returns:

$A$1
$B$1
$C$1
$A$2
$B$2
$C$2
$A$3
$B$3
$C$3

As you would expect

Answer 3

I cannot find any proper documentation on this, but this observed behaviour actually appears to be very logical.

The Range class in Excel has two important properties:

• A single instance of Range is enough to represent any possible range on a sheet
• It is iterable (can be used in a For Each loop)

I believe that in order to achieve logically looking iterability and yet avoid creating unnecessary entities (i.e. separate classes like CellsCollection, RowsCollection and ColumnsCollection), the Excel developers came up with a design where each instance of Range holds a private property that tells it in which units it is going to count itself (so that one range could be "a collection of rows" and another range could be "a collection of cells").

This property is set to (say) "rows" when you create a range via the Rows property, to (say) "columns" when you create a range via the Columns property, and to (say) "cells" when you create a range in any other way.

This allows you to do this and not become unnecessarily surprised:

For Each r In SomeRange.Rows
  ' will iterate through rows
Next

For Each c In SomeRange.Columns
  ' will iterate through columns
Next

Both Rows and Columns here return the same type, Range, that refers to the exactly same sheet area, and yet the For Each loop iterates via rows in the first case and via columns in the second, as if Rows and Columns returned two different types (RowsCollection and ColumnsCollection).

It makes sense that it was designed this way, because the important property of a For Each loop is that it cannot provide multiple parameters to a Range object in order to fetch the next item (cell, row, or column). In fact, For Each cannot provide any parameters at all, it can only ask "Next one please."

To support that, the Range class had to be able to give the next "something" without parameters, even though a range is two-dimensional and needs two coordinates to fetch the "something." Which is why each instance of Range has to remember in what units it will be counting itself.

A side effect of that design is that it is perfectly fine to look up "somethings" in a Range providing only one coordinate. This is exactly what the For Each mechanism would do, we are just directly jumping to the ith item.
When iterating over (or indexing into) a range returned by Rows, we're going to get the ith row, from top to bottom; for a range returned by Columns we're getting the ith column, from left to right; and for a range returned by Cells or by any other method we're going to get the ith cell, counting from top left corner to the right and then to the bottom.

Another side effect of this design is that can "step out" of a range in a meaningful way. That is, if you have a range of three cells, and you ask for the 4th cell, you still get it, and it will be the cell dictated by the shape of the range and the units it's counting itself in:

Dim r As Range
Set r = Range("A1:C3")          ' Contains 9 cells

Debug.Print r.Cells(12).Address ' $C$4 - goes outside of the range but maintains its shape

---

So your workaround of Set SpecificRow = Intersect(SpecificRow, SpecificRow) resets the internal counting mode of that specific Range instance from (say) "rows" to (say) "cells".

You could have achieved the same with

Set SpecificRow = SpecificRow.Cells
MsgBox SpecificRow(1).Address

But it's better to keep the Cells close to the point of usage rather than the point of range creation:

MsgBox SpecificRow.Cells(1).Address