SUM/COUNT of Value 1 at the Start of Set

[ibmy]

Hi Experts,

How to get result in Column L (Start) ?

Start Assign.xlsb
	G		I			L
1	Data		End			Start
2
3	1					5
4	1
5	1
6	1
7	1		5
8
9	1		1			1
10
11
12	1					2
13	1		2
14
15
16	1		1			1
17
18
19	1					2
20	1		2
21
22	1					3
23	1
24	1		3
25
26
27
090621

For Column I ( End) , I use this code :

Sub End2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("G2", Range("G" & Rows.Count).End(xlUp).Offset(1)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 2 To UBound(a)
    If a(i, 1) = 1 Then
      k = k + 1
    Else
      If a(i - 1, 1) = 1 Then
        b(i - 1, 1) = k
        k = 0
      End If
    End If
  Next i
  Range("I2").Resize(UBound(b)).Value = b
End Sub

Column G ( Data) contain value 1 and blank cell.
Real data is 200k-250k row.

 

[iggydarsa]

Would this do the job for you?

Sub Start2()
    Dim lr As Long: lr = Range("G" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Dim v As Long: v = 0
    
    For i = lr To 2 Step -1
        If v = 0 Then
            If Range("I" & i).Value > 0 Then
                v = Range("I" & i).Value
            End If
            If Range("G" & i).Offset(-1).Value = "" Or Range("G" & i).Offset(-1).Value = "Data" Then
                If v <> 0 Then Range("L" & i).Value = v
                v = 0
            End If
        Else
            If Range("G" & i).Offset(-1).Value = "" Or Range("G" & i).Offset(-1).Value = "Data" Then
                If v <> 0 Then Range("L" & i).Value = v
                v = 0
            End If
        End If
    Next i
End Sub

 

[ibmy]

Thanks @iggydarsa
The code is working.