VBA for changing a defined name reference

R

Roy Miller

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
 
D

Don Guillett

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.
 
T

Tom Ogilvy

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"
 
Top