Range Name

[JT]

I have the following code in my macro:

ActiveWorkbook.Names.Add Name:="Range", RefersToR1C1:= _
"='Sheet1'!R7C1:R125C1"

The problem is the last row of the range will change each
time I run the macro. I have tried finding the last row
of the range and using a variable like this:

ActiveWorkbook.Names.Add Name:="Range", RefersToR1C1:= _
"='AR List'!R7C1:R(lstrow)C1"

But there is something wrong with the syntax because that
isn't correct either.

Any suggestions or ideas would be appreciated. Thanks for
the help........

 

[PaulD]

: I have the following code in my macro:
:
: ActiveWorkbook.Names.Add Name:="Range", RefersToR1C1:= _
: "='Sheet1'!R7C1:R125C1"
:
: The problem is the last row of the range will change each
: time I run the macro. I have tried finding the last row
: of the range and using a variable like this:
:
: ActiveWorkbook.Names.Add Name:="Range", RefersToR1C1:= _
: "='AR List'!R7C1:R(lstrow)C1"
<snip>

you can't put a variable in the middle of a string.
"='AR List'!R7C1:R" & lstrow & "C1"

Paul D

 

[Bob Phillips]

Worksheets("AR List").Range("A7:A" & lstrow).Name = "Range"

--

HTH

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