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

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.



---
Regards.
Norman
"ExcelMonkey" wrote in message
...
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

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


"ExcelMonkey" wrote:

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

On Jun 2, 12:53*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"ExcelMonkey" wrote:
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- Hide quoted text -

- 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)

Thanks for pointing that out. A much better solution.
--
HTH...

Jim Thomlinson


"JW" wrote:

On Jun 2, 12:53 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"ExcelMonkey" wrote:
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- Hide quoted text -

- 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)

Thanks to all you responded.

EM

"Norman Jones" wrote:

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.



---
Regards.
Norman
"ExcelMonkey" wrote in message
...
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