Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Named Range using VB
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Named Range using VB
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Named Range using VB
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Named Range using VB
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Named Range using VB
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a named range gives an error only when the filename is lo | Excel Discussion (Misc queries) | |||
Creating a named range? | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel |