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/