Hello,

I have a situation where the user makes a selection in column
"A" (rng1). I need to create parallel ranges of this selection in
other columns on the same sheet (rng2). The following line of code
works well for single area selections, but only sets the first area on
multiple area selections.

Set rng2 = Range(rng1.Address).Offset(, col - 1)

I can accomplish what I want by looping through each area in column A,
but this seems to be somewhat inefficient when there's a lot of
columns involved. There must be a better way (hopefully). Thank you
for your suggestions.

I'm using Excel 97

Regards,

Dave

This

Set Rng2 = Rng1.Offset(, Col - 1)

worked fine for me:

Sub TryNow()
Dim Rng1 As Range
Dim Rng2 As Range
Dim Col As Integer

Col = 3

Set Rng1 = Range("A1:A3,B10:B12")
MsgBox Rng1.Address
Set Rng2 = Rng1.Offset(, Col - 1)
MsgBox Rng2.Address

End Sub


--
HTH,
Bernie
MS Excel MVP


"Dave Unger" wrote in message
oups.com...
Hello,

I have a situation where the user makes a selection in column
"A" (rng1). I need to create parallel ranges of this selection in
other columns on the same sheet (rng2). The following line of code
works well for single area selections, but only sets the first area on
multiple area selections.

Set rng2 = Range(rng1.Address).Offset(, col - 1)

I can accomplish what I want by looping through each area in column A,
but this seems to be somewhat inefficient when there's a lot of
columns involved. There must be a better way (hopefully). Thank you
for your suggestions.

I'm using Excel 97

Regards,

Dave

Hi Bernie,

Thanks for your reply, but this is still a problem for me.

Running your macro:

Set Rng1 = Range("A1:A3,B10:B12")
MsgBox Rng1.Address returns "$A$1:$A$3,$B$10:$B$12"

and

Set Rng2 = Rng1.Offset(, Col - 1)
MsgBox Rng2.Address returns "$C$1:$C$3"

Do you suppose this is a limitation of Excel 97?

regards,

Dave

Do you suppose this is a limitation of Excel 97?

Yes it is. You'd have to approach it like this:

Sub a()
Dim Rng1 As Range, Rng2 As Range
Dim Area As Range
Set Rng1 = Range("A1:A3,B10:B12")
MsgBox Rng1.Address

For Each Area In Rng1.Areas
If Rng2 Is Nothing Then
Set Rng2 = Area
Else
Set Rng2 = Union(Area, Area.Offset(, 5))
End If
Next
MsgBox Rng2.Address
End Sub


--
Jim
"Dave Unger" wrote in message
oups.com...
| Hi Bernie,
|
| Thanks for your reply, but this is still a problem for me.
|
| Running your macro:
|
| Set Rng1 = Range("A1:A3,B10:B12")
| MsgBox Rng1.Address returns "$A$1:$A$3,$B$10:$B$12"
|
| and
|
| Set Rng2 = Rng1.Offset(, Col - 1)
| MsgBox Rng2.Address returns "$C$1:$C$3"
|
| Do you suppose this is a limitation of Excel 97?
|
| regards,
|
| Dave
|
|

Hi Jim,

Thanks for your reply - I suspected this was route I'd have to go -
one of these days I'll update to a later version.

regards,

Dave

On Mar 26, 2:36 pm, "Jim Rech" wrote:
Do you suppose this is a limitation of Excel 97?

Yes it is. You'd have to approach it like this:

Sub a()
Dim Rng1 As Range, Rng2 As Range
Dim Area As Range
Set Rng1 = Range("A1:A3,B10:B12")
MsgBox Rng1.Address

For Each Area In Rng1.Areas
If Rng2 Is Nothing Then
Set Rng2 = Area
Else
Set Rng2 = Union(Area, Area.Offset(, 5))
End If
Next
MsgBox Rng2.Address
End Sub

--
Jim"Dave Unger" wrote in message

oups.com...
| Hi Bernie,
|
| Thanks for your reply, but this is still a problem for me.
|
| Running your macro:
|
| Set Rng1 = Range("A1:A3,B10:B12")
| MsgBox Rng1.Address returns "$A$1:$A$3,$B$10:$B$12"
|
| and
|
| Set Rng2 = Rng1.Offset(, Col - 1)
| MsgBox Rng2.Address returns "$C$1:$C$3"
|
| Do you suppose this is a limitation of Excel 97?
|
| regards,
|
| Dave
|
|