G'day there One and All,

I have a smallish problem upon which has brought my undeveliped
programming skills to a halt. Hence, here I am.

I have a roster sheet with a row of dates across one of the top
rows. There could be 14 or 28 dates, depending on the users'
requirements, and there is no consistency as to which row might be used.
There may even be another row further down the sheet.

I need to search those dates and then scan down the columns under
the date being tested. That bit works. However I've found that some of
my users have a duplicated the row of dates with one row formatted for
the date and another formatted for day of the week. This is fine, but my
code is detecting both rows and searching each one. Hence I get 2 hits
and 2 searches for each target date. The results are processed to remove
duplicates in any case, but it's an overhead I don't need.

I've written the following code:

Public Function dateRanges(objWKS As Worksheet) As Range
' Returns addresses of groups of contiguous ells containing dates
Dim cell As Range
Dim iX As Integer
Dim rngX
' Set to range consisting of all cells with date format
For Each cell In objWKS.UsedRange.Cells
If TypeName(cell.Value) = "Date" Then
If dateRanges Is Nothing Then
Set dateRanges = cell
Else
Set dateRanges = Union(dateRanges, cell)
End If
End If
Next
' Then reduce to set of contiguous cells
rngX = Split(dateRanges.Address, ",")
Set dateRanges = Nothing
For iX = LBound(rngX) To UBound(rngX)
If Not InStr(1, rngX(iX), ":") = 0 Then
If dateRanges Is Nothing Then
Set dateRanges = Range(rngX(iX))
Else
Set dateRanges = Union(dateRanges, Range(rngX(iX)))
End If
End If
Next
End Function

...which gives me a range of all dates that occur in contiguous
cells. Single cells with dates are excluded. This is what I want.

However, what I now need is some way to remove the 2nd row from a
range such as "A4:AE5". I only need the top or bottom row, not both. I
was thinking a judicious application of - dateRanges.row - might be
useful, but I can't figure out how to make it work.

If it should happen that I get a union for 2 rows of dates, eg.
"A4:AE5, A20:AE21" I can easily use split as above and process each
single range before reassembling the components. However firstly I need
to remove one of the rows and that's got me stuck.

Any suggestions will be gratefully accepted (except those
suggestions involving my anatomy <g ).

--
Thanks for listening,
See ya,
Ken McLennan
Qld, Australia