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

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
On Feb 19, 6:33*pm, Keith <alpine7...@hotmail.com> wrote:
> 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

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Keith" wrote:

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

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


"Keith" wrote:

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

On Feb 19, 1:55*pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Keith" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

Got it. Thanks all.

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

On Tue, 19 Feb 2008 10:33:30 -0800 (PST), Keith <(E-Mail Removed)> wrote:

>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