Greetings,

I have a vertical range in a column called MyRange. I am trying to get it
resized to reflect items added to the bottom. I tried

With Range("MyRange").Resize(1,1)
.Parent.Range(.Item(1), End(xlUp)).Name = "MyRange"
End With

This gives me a cell. How do you get this to work? Any reference material
on how to do this?

Thanks in advance for your help!

Ray

Try:

Sub Test()
With Range("MyRange")(1, 1)
.Resize(.End(xlDown).Row - .Row + 1, _
Range("MyRange").Columns.Count).Name = "MyRange"
End With
End Sub

--

Vasant


"Ray Batig" wrote in message
nk.net...
Greetings,

I have a vertical range in a column called MyRange. I am trying to get it
resized to reflect items added to the bottom. I tried

With Range("MyRange").Resize(1,1)
.Parent.Range(.Item(1), End(xlUp)).Name = "MyRange"
End With

This gives me a cell. How do you get this to work? Any reference material
on how to do this?

Thanks in advance for your help!

Ray

You almost had it:

Sub Tester5()
Debug.Print Range("MyRange").Address

With Range("MyRange").Resize(1, 1)
.Parent.Range(.Item(1), .End(xlDown)).Name = "MyRange"
End With

Debug.Print Range("MyRange").Address
End Sub

Produced:

$A$1:$A$10
$A$1:$A$12

this would assume MyRange is more than one cell and it it is contiguous.

--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
nk.net...
Greetings,

I have a vertical range in a column called MyRange. I am trying to get it
resized to reflect items added to the bottom. I tried

With Range("MyRange").Resize(1,1)
.Parent.Range(.Item(1), End(xlUp)).Name = "MyRange"
End With

This gives me a cell. How do you get this to work? Any reference material
on how to do this?

Thanks in advance for your help!

Ray

Is this what u want ?

Range(Range("MyRange").Resize(1, 1), Range("MyRange").Resize(1,
1).End(xlUp)).Name = "MyRange"

Thanks Tom,

I have
If Range("MyRange").Cells.Count =1 then
goto skip
else
this new resize code.

That takes care of a single cell range. In this case the data is contiguous.
If the are blanks, how would you rewrite?

Thanks again!

Ray

Tom Ogilvy wrote in message
...
You almost had it:

Sub Tester5()
Debug.Print Range("MyRange").Address

With Range("MyRange").Resize(1, 1)
.Parent.Range(.Item(1), .End(xlDown)).Name = "MyRange"
End With

Debug.Print Range("MyRange").Address
End Sub

Produced:

$A$1:$A$10
$A$1:$A$12

this would assume MyRange is more than one cell and it it is contiguous.

--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
nk.net...
Greetings,

I have a vertical range in a column called MyRange. I am trying to get
it
resized to reflect items added to the bottom. I tried

With Range("MyRange").Resize(1,1)
.Parent.Range(.Item(1), End(xlUp)).Name = "MyRange"
End With

This gives me a cell. How do you get this to work? Any reference
material
on how to do this?

Thanks in advance for your help!

Ray

Sub Tester6()
Debug.Print Range("MyRange").Address

With Range("MyRange").Resize(1, 1)
.Parent.Range(.Item(1), _
.Parent.Cells(Rows.Count, .Column) _
.End(xlUp)).Name = "MyRange"
End With

Debug.Print Range("MyRange").Address
End Sub

--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
. net...
Thanks Tom,

I have
If Range("MyRange").Cells.Count =1 then
goto skip
else
this new resize code.

That takes care of a single cell range. In this case the data is
contiguous.
If the are blanks, how would you rewrite?

Thanks again!

Ray

Tom Ogilvy wrote in message
...
You almost had it:

Sub Tester5()
Debug.Print Range("MyRange").Address

With Range("MyRange").Resize(1, 1)
.Parent.Range(.Item(1), .End(xlDown)).Name = "MyRange"
End With

Debug.Print Range("MyRange").Address
End Sub

Produced:

$A$1:$A$10
$A$1:$A$12

this would assume MyRange is more than one cell and it it is contiguous.

--
Regards,
Tom Ogilvy


"Ray Batig" wrote in message
nk.net...
Greetings,

I have a vertical range in a column called MyRange. I am trying to get
it
resized to reflect items added to the bottom. I tried

With Range("MyRange").Resize(1,1)
.Parent.Range(.Item(1), End(xlUp)).Name = "MyRange"
End With

This gives me a cell. How do you get this to work? Any reference
material
on how to do this?

Thanks in advance for your help!

Ray