Solved: Equivalent Range on specific row

[Adamski]

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]

With Head being a named range of D4:H4, the following will select D7:H7

[VBA]range("head").offset(3).Select[/VBA]

[mdmackillop]

I'd probably use this. I can't see there being something much simpler than you describe.
[vba]
rw = rw - Range("Head").Row
Range("Head").Offset(rw).Select

[/vba]

[Adamski]

Cheers, that's what I figured.

Thanks

[Paul_Hossler]

Also

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

Paul