Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for changing a defined name reference
Sorry if this has been asked before, but is it possible and if so ho
can it be done to change the reference through VBA of a defined name for instance, I have a list called 'People' and it resides on one of m worksheets at $a10:$a60 and I want to be able to change the th reference to say $a10:$a59 if I wanted to decrease the reference of th list by one cell or alterantely if I wanted to increase by one cell t $a10:$a61 if I wanted to increase the list size. Thanks in advance Henr -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for changing a defined name reference
try this
ActiveWorkbook.Names.Add Name:="People", RefersTo:="=sheet2!$A$10:$a$61" but you would be better off if you used a defined name =offset($a$10,0,0,counta($A:$A)+5,1) you would need to modify the +5 to suit your needs. Test by using f5 goto people. -- Don Guillett SalesAid Software "Roy Miller " wrote in message ... Sorry if this has been asked before, but is it possible and if so how can it be done to change the reference through VBA of a defined name, for instance, I have a list called 'People' and it resides on one of my worksheets at $a10:$a60 and I want to be able to change the the reference to say $a10:$a59 if I wanted to decrease the reference of the list by one cell or alterantely if I wanted to increase by one cell to $a10:$a61 if I wanted to increase the list size. Thanks in advance Henri --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for changing a defined name reference
Dim rng as Range
set rng = Range("People") rng.resize(rng.rows.count-1).Name = "People" or to increase Dim rng as Range set rng = Range("People") rng.Resize(rng.rows.count+1).Name = "People" -- Regards, Tom Ogilvy "Roy Miller " wrote in message ... Sorry if this has been asked before, but is it possible and if so how can it be done to change the reference through VBA of a defined name, for instance, I have a list called 'People' and it resides on one of my worksheets at $a10:$a60 and I want to be able to change the the reference to say $a10:$a59 if I wanted to decrease the reference of the list by one cell or alterantely if I wanted to increase by one cell to $a10:$a61 if I wanted to increase the list size. Thanks in advance Henri --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Defined Names | Excel Worksheet Functions | |||
Need to use the reference in a defined name in adifference colum | Excel Discussion (Misc queries) | |||
Changing a Defined Name | Excel Discussion (Misc queries) | |||
Updating cells which reference a defined name | Excel Discussion (Misc queries) | |||
Changing Multiple Defined Names At Once? | Excel Discussion (Misc queries) |