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

try this code


Code
-------------------
Sub NameARange()
Range("AD2:AD" & Range("AD2").End(xlDown).Row).Name = "MyName"
End Su
-------------------


Hope this helps,

Wil

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

or...

instead of RefersToR1C1

use

RefersTo:=Range("A1:B2"

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

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

Hi Adriaan,

Try this code. Assume [AD2] is not blank,

Sub CreateName()

Dim cell As Range, rng As Range, sName As String

sName = "DropListLoc"

Set cell = [AD2]

Set rng = ActiveSheet.Range(cell, cell.End(xlDown))

If Application.CountA(rng) 1 Then

rng.Name = sName

Else

cell.Name = sName

End If

End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/
(Excel Add-ins)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!