Passing a variable to a range statement
 

Please could you tell me the correct syntax to use in this bit of code where
I am trying to define a named range "Jobs" which is A1 to row number
NoOfRows. A macro recorded this bit of code below and I am trying to
replace the "R360" with something like RNoOf Rows. R&NoOfRows does not
work.

ActiveWorkbook.Names.Add Name:="Jobs", RefersToR1C1:="=sheet1!R1C1:R360C1"

Passing a variable to a range statement
 

One way

Sub test()
Dim NoOfRows As Long
NoOfRows = 10
Range("a1:a" & NoOfRows).Name = "Jobs"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Mervyn Thomas" wrote in message ...
Please could you tell me the correct syntax to use in this bit of code where
I am trying to define a named range "Jobs" which is A1 to row number
NoOfRows. A macro recorded this bit of code below and I am trying to
replace the "R360" with something like RNoOf Rows. R&NoOfRows does not
work.

ActiveWorkbook.Names.Add Name:="Jobs", RefersToR1C1:="=sheet1!R1C1:R360C1"

Passing a variable to a range statement
 

Place the Sheet name also in the code

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
One way

Sub test()
Dim NoOfRows As Long
NoOfRows = 10
Range("a1:a" & NoOfRows).Name = "Jobs"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Mervyn Thomas" wrote in message ...
Please could you tell me the correct syntax to use in this bit of code where
I am trying to define a named range "Jobs" which is A1 to row number
NoOfRows. A macro recorded this bit of code below and I am trying to
replace the "R360" with something like RNoOf Rows. R&NoOfRows does not
work.

ActiveWorkbook.Names.Add Name:="Jobs", RefersToR1C1:="=sheet1!R1C1:R360C1"

Passing a variable to a range statement
 

Try

ActiveWorkbook.Names.Add Name:="Jobs", RefersToR1C1:="=sheet1!R1C1:R" &
NoOfRows & "C1"

Steve

"Mervyn Thomas" wrote in message
...
Please could you tell me the correct syntax to use in this bit of code
where
I am trying to define a named range "Jobs" which is A1 to row number
NoOfRows. A macro recorded this bit of code below and I am trying to
replace the "R360" with something like RNoOf Rows. R&NoOfRows does not
work.

ActiveWorkbook.Names.Add Name:="Jobs", RefersToR1C1:="=sheet1!R1C1:R360C1"