Hi;

I have a range name $B$5:$B$?. ? means the length of the range can change.
I want to create a new temporary range that depends on the length of the
original range name i.e. $A$5:$A$?. I should know how to do this, but
tonight, Gawd if I can make it work.

Any suggestions?

Regards Bill

Where YourRange refers to A5:A?, try
=OFFSET(Sheet1!$B$5,0,0,ROWS(YourRange),1)
or
=OFFSET(YourRange,0,1)
or, programmatically
Range("YourRange").Offset(0, 1)




"Bill Case" wrote:

Hi;

I have a range name $B$5:$B$?. ? means the length of the range can change.
I want to create a new temporary range that depends on the length of the
original range name i.e. $A$5:$A$?. I should know how to do this, but
tonight, Gawd if I can make it work.

Any suggestions?

Regards Bill

My explanation in my first post wasn't very clear. Let me try again.

I have RangeName as $B$5:$B$?

I want to :

Dim ARange as Range
Set ARange = RangeName only as $A$5:$A$?

I can do this with Cells( i, 0) and a For loop but there should be a better
way.

Regards Bill


"Bill Case" wrote:

Hi;

I have a range name $B$5:$B$?. ? means the length of the range can change.
I want to create a new temporary range that depends on the length of the
original range name i.e. $A$5:$A$?. I should know how to do this, but
tonight, Gawd if I can make it work.

Any suggestions?

Regards Bill

Dim orgRange As Range
Dim newRange As Range
Dim newString As String

Set orgRange = ActiveSheet.Range("$B$5:$B$45")
newString = Replace(orgRange.Address, "B", "A", 1, -1, vbBinaryCompare)
Set newRange = ActiveSheet.Range(newString)




"Bill Case" schreef in bericht
...
My explanation in my first post wasn't very clear. Let me try again.

I have RangeName as $B$5:$B$?

I want to :

Dim ARange as Range
Set ARange = RangeName only as $A$5:$A$?

I can do this with Cells( i, 0) and a For loop but there should be a
better
way.

Regards Bill


"Bill Case" wrote:

Hi;

I have a range name $B$5:$B$?. ? means the length of the range can
change.
I want to create a new temporary range that depends on the length of the
original range name i.e. $A$5:$A$?. I should know how to do this, but
tonight, Gawd if I can make it work.

Any suggestions?

Regards Bill

Hi JMB;

Pushing 'Yes' for "Was this post helpful to you?" seems inadequate. It's
late here; I promised I would be finished my little project by the end of the
weekend; can't think anymore. I really appreciate your help.

Regards Bill

set ARange = RangeName.offset(0,-1)

--
Regards,
Tom Ogilvy


"Bill Case" wrote in message
...
My explanation in my first post wasn't very clear. Let me try again.

I have RangeName as $B$5:$B$?

I want to :

Dim ARange as Range
Set ARange = RangeName only as $A$5:$A$?

I can do this with Cells( i, 0) and a For loop but there should be a
better
way.

Regards Bill


"Bill Case" wrote:

Hi;

I have a range name $B$5:$B$?. ? means the length of the range can
change.
I want to create a new temporary range that depends on the length of the
original range name i.e. $A$5:$A$?. I should know how to do this, but
tonight, Gawd if I can make it work.

Any suggestions?

Regards Bill