Finding longest sequence of the same value in a row

[mrshift]

Hi all,

Hope someone can please help with this as I've spent hours scratching my head.

I'm looking for a formula that calculates the green values in the J column (which I've added in manually). I need the formula to please find the longest streak in the row of consecutive instances of the same positive integer value, i.e. consecutive blank cells are not counted.

	A	B	C	D	E	F	G	H	I	J
1	2						4	4	4	3
2	1	2	5	5	5	5		5	5	4
3	2	3	2	6	6	6	2	3	2	3

The integer itself with the longest streak in each column isn't known, nor am I interested in knowing this - just the length of the longest unbroken sequence of that integer, please.

Hope that makes sense, and thanks in advance!

 

[JoeMo]

I assume you will want to use Peter's worksheet functions solution, but for the record, the reason the UDF I proposed fails is because your "empty" (blank) cells contain formulas that return the null string ('"") which you did not mention in your OP.

Below is a modified UDF that I've only tested successfully on the data you furnished in Post #5. Should you have time to test it on your more extensive data, I'd be curious to know if it works for you.

Function MaxLength(R As Range) As Variant
Dim V As Variant, i As Long, ct As Long
V = R.Value
For i = 1 To UBound(V, 2) - 1
    If V(1, i) = "" Then ct = 0
    If V(1, i) <> "" And V(1, i + 1) <> "" Then
        If V(1, i) > 0 And V(1, i) = V(1, i + 1) Then
            If ct = 0 Then ct = 1
            ct = ct + 1
            If ct > MaxLength Then MaxLength = ct
        Else
            ct = 0
        End If
    End If
Next i
If MaxLength = 0 And Application.Sum(R) > 0 Then MaxLength = 1
End Function