Biff, I appreciate your efforts and all is working fine but the UDF
from Robert is an efficient way of doing it.

As far as speed of processing is concerned, I am not sure if UDF takes
long time of the array formula.

Robert, one question for you.

I didn't understand the line Dim myarray(1 To 12) As Integer

Why 1 to 12?

Also the data what I gave was just a sample data. I have around 3160
rows in my file. When I used Biff's suggestion, everything works fine
but your UDF does not give me correct results for 366 rows out of 3160.


In my file I have 3160 rows and 25+ columns. There can be less than 25
columns in a row or there can be more. Few columns are BLANK but I
guess your If myarray(Month(cell.Value)) < 0 Then line will take care
of it.

I am just wondering why I am not getting correct result for 366 rows.
Do I have to make changes in the Dim myarray(1 To 12) As Integer line?

Maxi

Maxi
The array
Dim myarray(1 To 12) As Integer
is equivalent to myarray(jan to dec) and stores a day value in each.
The reason you were getting incorrect results from the UDF I posted is I
had not protected against blank cells
A revised version is posted below.

The logic is as follows
Set up a data store for each possible month to hold the day value
loop through each cell in the range in turn
if there is a positive value in the cell continue if not skip to the next
cell
using the month value of the cell look in the array
if the entry in the array is not 0 then make it equal to the lowest of
either the current array value or the day value of the cell
otherwise put in the day value of the cell
when all the cells have been checked return the largest value in the array


***************************

Function MaxDate(myRange As Range) As Integer

Dim myarray(1 To 12) As Integer
For Each cell In myRange
If cell.Value 0 Then
If myarray(Month(cell.Value)) < 0 Then
myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value), _
myarray(Month(cell.Value)))
Else
myarray(Month(cell.Value)) = Day(cell.Value)
End If
End If
Next cell

MaxDate = WorksheetFunction.Max(myarray)

End Function

**************************

I am sure some of the gurus could simplify the logic but for now I hope
this does the trick.

let us know if it works
cheers RES

Works !!!

Thanx