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

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/

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/