Understanding Resize Method

[ExcelMonkey]

Why does using the Resize Method below return a cell address of $M$2? I
would have thought it would keep the addres the same. Also, why does it
absolute the range?

revrangeaddress = Range("M2:M13").Resize(1, 1).Address

Thanks

EM

 

[Norman Jones]

Hi Excel Monkey..

The expression:

Range("M2:M13").Resize(1, 1).

will return a ramge, anchored in the firrst
cell of the host range, which is one one
row long and one column wide. i.e. a single
cell.

 

[Jim Thomlinson]

You have a couple of questions...
1. Why do you get $M$2 as your result. Resize can be used to expand or
contract a range. To that end it uses the upper left corner of the range as
the starting point and allows you to resize from that starting point. To get
the same size you would want...

with Range("M2:M13")
revrangeaddress = .Resize(.rows, .columns).Address
end with

2. Why absolute? Address always returns an absolute. If you need the $ signs
removed you can just use the substitute function.

 

[JW]

You have a couple of questions...
1. Why do you get $M$2 as your result. Resize can be used to expand or
contract a range. To that end it uses the upper left corner of the range as
the starting point and allows you to resize from that starting point. To get
the same size you would want...

with Range("M2:M13")
revrangeaddress = .Resize(.rows, .columns).Address
end with

2. Why absolute? Address always returns an absolute. If you need the $ signs
removed you can just use the substitute function.

--
HTH...

Jim Thomlinson






- Show quoted text -

Nice explanation Jim. Only thing I will add is that you don't have to
use the Substitute function to get rid of the absolute references.
Just add False, False to the Address.
revrangeaddress = Range("M2:M13").Resize(1, 1).Address(False, False)

 

[Jim Thomlinson]

Thanks for pointing that out. A much better solution.

 

[ExcelMonkey]

Thanks to all you responded.

EM

Hi Excel Monkey..

The expression:

Range("M2:M13").Resize(1, 1).

will return a ramge, anchored in the firrst
cell of the host range, which is one one
row long and one column wide. i.e. a single
cell.