Search code examples
excelvariablesrangeoffsetvba

Excel VBA: Using .Range.Offset property on a range variable returns unexpected values

I have given Stack Exchange and Google a good search and I can't find the exact answer I'm looking for.

I'm having trouble understanding the values returned from using RangeVariable.Range.Offset, NOT Worksheet.Range.Offset

I define a range from A2 to J10, so skipping the first row:

Dim myRange As Range
Set myRange = ActiveSheet.Range("A2", "J10")

So now I test using .Offset(0 rows, 9 columns):

MsgBox myRange.Range("A1").Offset(0, 9).Address

I return "$J$2" as expected, seeing as "A1" of this range is "A2" on the sheet itself.

But now I use the above as the second argument of another .Range property test:

MsgBox myRange.Range("A1", myRange.Range("A1").Offset(0, 9)).Address

I return "$A$2:$J$3", so it looks like its skipped a row calculating the second argument, and it ends up being a 2 row range even though the row offset was zero.

If I change the row offset to -1 it returns "$A$2:$J$2", so only the one row.

I usually use Worksheet.Range to define ranges and it always works as expected. Can anybody think why using a RangeVariable.Range.Offset for the second argument of a range property might work this way?

Cheers, Stephen

Solution

  • I vaguely remember a previous question along these lines and i don't recall if it was answered, but this gives the expected output:

    Range(myRange.Range("A1"), myRange.Range("A1").Offset(0, 9)).Address

    EDIT: this was it - selecting a range within a range

    EDIT2: I'd find this easier to work with - 

    myRange.Range("A1").Resize(1,10).Address