Adrian ,

Try

Dim sRange As String
With ActiveSheet
sRange = .Range("AD2", .Range("AD2").End(xlDown)).Address
ActiveWorkbook.Names.Add Name:="DropListLoc", _
RefersTo:="=" & sRange
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Adriaan van der Linde" wrote in
message ...
Hi Guys

I want to create a Named Range making use of a dynamic
amount of rows in a sheet.

The examples I have will only accept a predefined range.

' I can select the range I want to name
ActiveSheet.Range("AD2", ActiveSheet.Range("AD2").End
(xlDown)).Select

' I now want to take that selection and name the
range
ActiveWorkbook.Names.Add Name:="DropListLoc",
RefersToR1C1:="=NewProject!R9C5"

This will only name the "C5" cell

Thanks a stack

Adriaan