Passing a variable to a range statement

[Mervyn Thomas]

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"

 

[Ron de Bruin]

One way

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

 

[Ron de Bruin]

Place the Sheet name also in the code

 

[Steve Smallman]

Try

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

Steve