I'm trying to get a macro to change the area referred to by a named
range.

The variables would be

The topleft cell which would be a static named range on a worksheet (eg
TOP_LEFT)

The number of rows the range had which would be an input on the
worksheet

The number of columns the range had which would be an input on the
worksheet

The range on the worksheet I would want to change would be NAMEDRANGE

Thanks in advance for any help

Paul

Set rng = Range("TOP_LEFT").Resize(Range("A1").Value,Range(" A2").Value)
rng.Name = "NAMEDRANGE"

assuming that A1 is the number of rows, A2 is columns.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul Chapman" wrote in message
oups.com...
I'm trying to get a macro to change the area referred to by a named
range.

The variables would be

The topleft cell which would be a static named range on a worksheet (eg
TOP_LEFT)

The number of rows the range had which would be an input on the
worksheet

The number of columns the range had which would be an input on the
worksheet

The range on the worksheet I would want to change would be NAMEDRANGE

Thanks in advance for any help

Paul

Smashing thankyou very much for your time

Paul -

You don't need a macro to do this.

Ctrl+F3 (Define Names Dialog)

Name:
NAMEDRANGE

Refers To:
=OFFSET(Sheet1!TOP_LEFT,0,0,Sheet1!NUMROWS,Sheet1! NUMCOLS)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Paul Chapman wrote:

I'm trying to get a macro to change the area referred to by a named
range.

The variables would be

The topleft cell which would be a static named range on a worksheet (eg
TOP_LEFT)

The number of rows the range had which would be an input on the
worksheet

The number of columns the range had which would be an input on the
worksheet

The range on the worksheet I would want to change would be NAMEDRANGE

Thanks in advance for any help

Paul