Sir,

Admittedly, I initially hadn't expected to need a UDF.
This, however, is the smartest approach simply because any
alternative (traditional- funtion) method, would result in an
augmented formula, which would in turn cause even the most
powerful machines to halt. (When updating the 12000 rows)

I effected your UDF.
Unfortunately, there seems to be a syntax error.
(Probably related to the first statement line)
I regrettably must also ask you to consider altering the syntax
altogether, as your (the previous) reply, reffered to my initial and
incomplete question.
The complete question can be found under my reply to Mr. Coderres'
2nd reply.
Lastly, I must clarify that I am not knowledgeable enough to alter or
correct your UDF myself.

I cannot but be both overwhelmed and hopeful by and of a
further, forward-pressing response.




"JMB" wrote:

A UDF that appears to work okay:

syntax is:
=maxinterval(A1:A12000,"larry")



Function MaxInterval(rngData As Range, _
varCriteria As Variant) As Long
Dim lngLast As Long
Dim i As Long

lngLast = 0

For i = 1 To rngData.Rows.Count
If rngData(i, 1) = varCriteria Then
MaxInterval = Application.Max(MaxInterval, i - lngLast - 1)
lngLast = i
ElseIf i = rngData.Rows.Count Then
MaxInterval = Application.Max(MaxInterval, i - lngLast)
End If
Next i

End Function



"Vasilis Tergen" wrote:

How would I find the maximum amount of intervals between
"Larry" re- appearing in column A?
I must use a formula- not a macro or a filter.

The correct answer in this example= 4.
That means that in column A, the maximum absence (or intervals)
of "Larry" re- appearing was 4 times, which occured between rows
2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once)


Rows Column A

1 John
2 Larry
3 John
4 John
5 John
6 Mary
7 Larry
8 Mary
9 Larry
10 John