New Range resize Question

[Ray Batig]

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

 

[Vasant Nanavati]

Try:

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

 

[Tom Ogilvy]

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.

 

[Guest]

Is this what u want ?

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

 

[Ray Batig]

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]

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