Creating a Named Range using VB

  • Thread starter Thread starter Adriaan van der Linde
  • Start date Start date
A

Adriaan van der Linde

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
 
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)
 
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)
 
Back
Top