Public Sub hello()
Dim lr As Long, lc As Long, arr As Variant, r As Long, dArr As Variant, tCount As Double, validSpace As Boolean
Dim c As Long, maxCount As Long, tempCount As Long, tempUbound As Long, curRow As Long, k As Long, uc As Long
tCount = Timer
Application.ScreenUpdating = False
With Sheet1
lr = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
lc = .UsedRange.SpecialCells(xlCellTypeLastCell).Column - 6
ReDim dArr(1 To .Rows.Count, 1 To 1)
curRow = 4
Do While tempUbound < lr
tempUbound = tempUbound + 50000
arr = .Range("G" & curRow & ":G" & WorksheetFunction.Min(tempUbound, lr)).Resize(, lc).Value
uc = UBound(arr, 2)
For r = 1 To UBound(arr) Step 1
maxCount = 0: tempCount = 0: validSpace = False
For c = 1 To uc Step 1
If Not arr(r, c) = Empty Then
If validSpace Then tempCount = tempCount + 1
Else
If tempCount > maxCount Then maxCount = tempCount
tempCount = 0: validSpace = True
End If
Next
k = k + 1
dArr(k, 1) = maxCount
Next
curRow = curRow + UBound(arr)
Loop
.Range("D4").Resize(k).Value = dArr
End With
Application.ScreenUpdating = True
MsgBox Timer - tCount
End Sub