都以9至13行為例
壹、普通循環方法
Sub Cycle()
Dim i As Long
Dim j As Long
Dim MaxCnt As Long
Dim arr As Variant
Dim Cell As Range
For Each Cell In Range("G9:G13")
arr = Application.Transpose(Application.Transpose(Cell.Resize(1, 8)))
Cells(Cell.Row, "C").Clear
For i = LBound(arr) To UBound(arr)
MaxCnt = 0
For j = i To UBound(arr)
If arr(j) > 0 Then
MaxCnt = MaxCnt + 1
Else
Exit For
End If
Next j
Cells(Cell.Row, "C") = Application.Max(MaxCnt, Cells(Cell.Row, "C"))
Next i
Next Cell
End Sub
二、遞歸方法
Sub TrasfRecursion()
Dim Cell As Range
For Each Cell In Range("G9:G13")
Cells(Cell.Row, "C").Clear
Call Recursion(Cell.Resize(1, 8))
Next
End Sub
Sub Recursion(Rng As Range)
Dim Cntius As Long
Dim arr As Variant
Dim i As Long
If Rng.Count = 1 Then
arr = Array(Rng.Value)
Else
arr = Application.Transpose(Application.Transpose(Rng))
End If
For i = LBound(arr) To UBound(arr)
If arr(i) <> 0 Then
Cntius = Cntius + 1
Else
Exit For
End If
Next
Cells(Rng.Row, "C") = Application.Max(Cntius, Cells(Rng.Row, "C"))
If Rng.Count > 1 Then
Recursion Rng.Offset(0, 1).Resize(1, Rng.Columns.Count - 1)
End If
End Sub
二者運行截圖
沒有使用幾十萬行數據測試，不知道哪種快。