VBA统计列下 0的最大连续出现次数

全党

1. <div class="blockcode"><blockquote>Sub shishi()
   
2. Dim crr()
   
3. Set dic = CreateObject("Scripting.Dictionary")
   
4. arr = Range("f1").CurrentRegion
   
5. For i = 2 To UBound(arr)
   
6. If arr(i, 1) <> 0 Then
   
7. dic(arr(i, 1) & "," & i) = i
   
8. End If
   
9. Next
   
10. brr = dic.items
    
11. n = 2
    
12. For j = UBound(brr) To 1 Step -1
    
13. n = n + 1
    
14. ReDim Preserve crr(1 To n + 2)
    
15. crr(1) = brr(0) - 2 - 1
    
16. crr(2) = UBound(arr) - brr(dic.Count - 1)
    
17. crr(n) = brr(j) - brr(j - 1) - 1
    
18. Next
    
19. [k2] = WorksheetFunction.Large(crr, 1)
    
20. [k3] = WorksheetFunction.Large(crr, 2)
    
21. [k4] = dic.Count
    
22. End Sub

复制代码

准提部林

連續0只有一種數, 只會填第一大//

Sub TEST_A1()
Dim Arr, xD, i&, M&, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([f1], [f65536].End(xlUp)(2))
For i = 2 To UBound(Arr)
    If Arr(i, 1) & "" = "0" Then N = N + 1: GoTo i01
    If N > 0 Then xD(N) = "": N = 0
    If Val(Arr(i, 1)) > 0 Then M = M + 1
i01: Next i
[k2:k3] = ""
For i = 1 To xD.Count
    Cells(i + 1, "k") = Application.Large(xD.keys, i)
    If i = 2 Then Exit For
Next i
[k4] = M
End Sub