Change the name of a data range

[Keith]

Ok...this probibly has a very simple solution but how do you change
the name of a data range? For example...say I have a range of cells A1
through A5 named "test". How can i change that to include cell A6 and
still keep the name of the range "test"? I can't figure it out.

Thanks,
Keith

 

[paul.robinson]

Hi
Range("A1:A6").Name = "test"

will overwrite the exisiting name. If you are doing something dynamic
you may prefer

Dim newrows as long
With Range("test")
newrows = .rows.count + 1
.resize(newrows).Name = "test"
end with

[code in "Excel 2002 VBA" Stephen Bullen et al]
this way, you don't need to hard code what "test" is.

regards
Paul

 

[John Bundy]

When stuck, use the macro recorder to do what you want and peek at the code.
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R5C1"
that is created when i name it, when i change it
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R6C1"
so you just need to add using the same name and change the reference.

 

[Gary''s Student]

Just re-create it:

Sub Macro1()
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R5C1"
ActiveWorkbook.Names("test").Delete
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R6C1"
End Sub

 

[Keith]

Just re-create it:

Sub Macro1()
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R5C1"
ActiveWorkbook.Names("test").Delete
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1:R6C1"
End Sub
--
Gary''s Student - gsnu2007d






- Show quoted text -

Got it. Thanks all.

 

[Gord Dibben]

Manually, insert>name>define.

Select the name and edit the Refers to: Sheet1!$A$1:$A$5 to $A$6


Gord Dibben MS Excel MVP