Saturday 13 February 2016

Excel vba: possible bug when creating a range as a sub-range of another range

I created the following sub to illustrate the problem simply. I assign the range A2:E10 of the active sheet to a range variable. Then, to another range variable, I assign the sub-range of this range, cells (1, 1) to (3, 3).



I would have expected that this would include the range A2 to C4 (since A2 is the first col, first row of the larger range). However, when I call the .Row method of each range's
first cell, I get different results: the larger range r returns "2", whereas the smaller range rSub returns "3".



(the .Row method returns the absolute row of the range it is called upon)




I would expect both these calls to return 2, as they should both refer to cell A2. Can anyone explain why this is not so?



*Edit: I have just altered the sub so that it calls the .Column method of each range's first cell, as it does the .Row method. This returns "1" for both ranges, as you might expect.



Sub test()
Dim r As Range
Set r = Range("A2:E10")

MsgBox r.Cells(1).Row '= 2


Dim rSub As Range
With r
Set rSub = .Range(.Cells(1, 1), .Cells(3, 3))
End With

MsgBox rSub.Cells(1).Row '= 3
End Sub

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...