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

Question

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

Tim Williams · Accepted Answer · 2017-01-18 05:49:57Z

1

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

edited Jan 18, 2017 at 5:49

answered Jan 17, 2017 at 7:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Over a year ago

Hi Tim, that's spot-on what I was after. I now have a better looking macro, I hate fudging logic just to get the right output. Using a row offset value of -1 to stay on the same row just looked ugly. Big Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related