Passing a variable to a range statement

  • Thread starter Thread starter Mervyn Thomas
  • Start date Start date
M

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"
 
One way

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

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

Steve
 
Back
Top