Solved: Get Subrange from a Range

[Adamski]

Hi,

I have a named range "DateAll" which is one row and a number of contigious columns. These cells contain ascending dates (1st of the month) of date type. I have two other named ranges "DateStart" and "DateEnd" which are individual cells containing a value from "DateAll".

I can loop "DateAll" to find the start and end cells but there must be a better/faster way (It has many columns and I need to do this often).

So, in VBA how do I get a range object of the subrange of "DateAll" from "DateStart" to "DateEnd"?

Thanks

[Bob Phillips]

Dim rng As Range
Dim cell As Range

Set cell = Range("DateAll").Find(What:=Range("DateStart").Text, LookIn:=xlValues)
If Not cell Is Nothing Then

Set rng = cell

Set cell = Range("DateAll").Find(Range("DateEnd").Text)
If Not cell Is Nothing Then

Set rng = Range(rng, cell)
End If

MsgBox rng.Address
End If

[Adamski]

Perfect. I was trying to get the find to work but wasn't using .text. Thanks