PDA

View Full Version : Solved: Equivalent Range on specific row



Adamski
12-15-2009, 08:50 AM
Hello,

I have a named range refering ("Head") to a number of contigious cells on one row ("D4:H4".

How do I get an equivalent range of the same columns but on a specific row?

eg. "D6:H6", or "D13:H13"

I can use Offset and subtract the "Head" row number, but is there a better way.

Thanks

mbarron
12-15-2009, 09:32 AM
With Head being a named range of D4:H4, the following will select D7:H7

range("head").offset(3).Select

mdmackillop
12-15-2009, 04:14 PM
I'd probably use this. I can't see there being something much simpler than you describe.

rw = rw - Range("Head").Row
Range("Head").Offset(rw).Select

Adamski
12-16-2009, 07:14 AM
Cheers, that's what I figured.

Thanks

Paul_Hossler
12-20-2009, 08:26 PM
Also


Sub drv()
Dim r As Range
Set r = Intersect([head].EntireColumn, Rows(7))
MsgBox r.Address
End Sub


Paul