MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Selecting multiple offset ranges?!?


Posted by Ben on September 28, 2001 9:32 AM

Can some one help me select multiple ranges?
I have a range of cells selected using:

Set LeftCell = cells(ActiveCell.Row, 5)
Set RightCell = cells(ActiveCell.Row, 256)

If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight)
If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft)
If LeftCell.column = 256 And RightCell.column = 1 Then ActiveCell.Select Else Range(LeftCell, RightCell).Select

and I would like to also be able to select cells in the same columns as this would select, but in the first row at the same time.


Posted by Ian on September 28, 2001 4:29 PM


Not really sure what you want, but see if any of the following does it. Replace your last line of code with :-

If leftcell.Column = 256 And rightcell.Column = 1 Then
Set R1 = ActiveCell
Else
Set R1 = Range(leftcell, rightcell)
End If
MsgBox R1.Address
Set R2 = R1.Offset(-R1.Row + 1, 0)
MsgBox R2.Address
Set R3 = Union(R1, R2)
MsgBox R3.Address