Syntax for using last row in R1C1 format 2

[LisaBee]

I'm sure there's something simple I'm missing . . . I got the formula for adding a named range using the macro editor, and edited it to account for the last row which will vary on different reports. This syntax isn't correct though for the cell address which refers to the Detail sheet. I've tried numerous variations to no avail. What is the correct syntax, or is there a different way to name a range without using the "RefersToR1C1"?

Dim lRow As Long
Range("T65000").Select
lRow = Selection.End(xlUp).Select
ActiveWorkbook.Names.Add Name:="Paper", RefersToR1C1:= _
"=Detail!R2C20:" & lRow & "C20"


Thanks very much!

 

[GVF]

Selection.end(xlUp).Select is returning -1 because it was successful. LRow is therefore always -1.
Change the .select to .row

Your syntax in the referstoR1C1 line is missing an "R" before the colon.

Range("T65000").Select
lRow = Selection.End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Paper", RefersToR1C1:= _
    "=Detail!R2C20:R" & lRow & "C20"

Greg

 

[SkipVought]

Hi,

With Range([T1], [T65536].End(xlUp))
  ActiveWorkbook.Names.Add _
    Name:="Paper", _
    RefersTo:="=" & .Parent.Name & "!" & .Address
End With

AVOID using the select method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LisaBee]

Thanks so much, that's exactly what I needed . . . appreciate all the input and comments!

 

[JTBorton]

Can someone please explain what this R1C1 format is? I have seen that a few times lately and am rather confused by it.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

There is A1 cell reference format and R1C1 cell reference format. Both A1 and R1C1 refer to the same cell. You must explicitly change the reference style in Tools > Options - General Tab.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}