Dynamic range naming in VB6.3 for Excel

[Paul Chapman]

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

 

[Bob Phillips]

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]

Smashing thankyou very much for your time

 

[Jon Peltier]

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/
_______