The below assumes that data is in column 1 and starts row
2 or lower. The answer will be in column 2 1 row above.

The macro replaces the ""'s with 0's to do the calcs and
rhe re-replaces the 0's with ""'s.

John

Sub Macro1()
'
' Macro1 Macro
'
Sheets("sheet1").Activate
Dim rng As Range
Range("b1").EntireColumn.ClearContents
Cells(1, 1).End(xlDown).CurrentRegion.Name = "data"
Range("data").Select
Selection.Replace What:="""""", Replacement:="0",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
toprow = Range("A1").End(xlDown).Row
bottomrow = ActiveSheet.Cells(Rows.Count, _
"A").End(xlUp).Row
start:
Set rng = Sheets("sheet1").Range("data").Find(" ")
If rng Is Nothing Then GoTo continue
rngrow = ActiveSheet.Range("rng").Row
Cells(rngrow, 1).EntireRow.Delete
If rngrow bottomrow Then GoTo continue
GoTo start
continue:
toprow = Range("A1").End(xlDown).Row
bottomrow = ActiveSheet.Cells(Rows.Count, _
"A").End(xlUp).Row
j = 0
For i = toprow To bottomrow
Cells(i, 1).Select
If Cells(i, 1).Value 0 Then j = j + 1
If Cells(i, 1).Value = 0 Then j = j
If Cells(i, 1).Value < 0 Then j = 0
Cells(i, 2).Value = j
GoTo nexti
nexti:
Next i
Range("data").Select
Selection.Replace What:="0", Replacement:="""""",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Max = 0
For i = toprow To bottomrow
If Cells(i, 2).Value Max Then Max = Cells(i, 2).Value
Next i
Cells(toprow - 1, 2).Value = Max
End Sub

-----Original Message-----
In Excel, my challenge is to count the maximum number of
consecutive
positive numbers in a column consisting of blank cells
and formulas that
evaluate to numbers and "".

For example, starting in A1, the cells evaluate to:

1
-2
3

5
-6

The result would be 2 (counting the two consecutive
positive numbers, 3 and
5, above and below the blank or "" cell).

This needs to be done using a single formula or array
formula, without using
a separate running total column using =MAX, and without
using a UDF. They
say it can't be done and I need to confirm that.

Thank you all,
JP


.