ranges and sub range

[Rod]

It must be Monday morning because I can't see the neat way to do this.


I have a range variable which is set to part of a row. i.e. I know it is
1-dimensional.

I want to set a new range variable to the first 5 cells in that range.

Set MyNewRng = MyOldRng( "5 left-hand-most cells")

I can only do it by making absolute references to the position of the cells
in the entire worksheet which all gets a bit messy and is far from elegant.



many thanks

Rod

 

[Frank Kabel]

Hi
try
Set MyNewRng = MyOldRng.resize(1,5)

 

[Robin Hammond]

Rod,

try something like this:

Sub RangeDemo()
Dim rngA As Range
Dim rngB As Range
Set rngA = ActiveSheet.Range("A1:AA1")
Set rngB = rngA(1, 1).Resize(1, 5)
MsgBox rngB.Address
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[Rod]

Of course!, so simple

Thank you

Rod

Hi
try
Set MyNewRng = MyOldRng.resize(1,5)