Concatenating fields based on consecutive values

Question

I have 2 columns, 200k rows:

F1  1
F2  0
F3  0
F4  0
F5  0
F6  1
F7  1
F8  0
F9  10

For all values = 0, I would like to concatenate the associated field with the previous associated field. When there are more consecutive fields (4), I would like all of the (5) relating fields to be concatenated together.

I want:

F1  1    
F2  0   F1|F2|F3|F4|F5
F3  0    
F4  0    
F5  0    
F6  1    
F7  1    
F8  0   F7|F8
F9  10   

Currently I have:

Sub mfewj()
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To N
    If Cells(i, 2) = 0 Then Cells(i, 3).FormulaR1C1 = "=CONCATENATE(R[-1]C[-2],""|"",RC[-2])"
Next i
End Sub

This returns:

F1  1    
F2  0   F1|F2
F3  0   F2|F3
F4  0   F3|F4
F5  0   F4|F5
F6  1    
F7  1    
F8  0   F7|F8
F9  10  

Any suggestions are appreciated

Answer 1

You have yo loop backwards through the list:

Option Explicit

Sub ConCatMacro()
Dim i As Long
Dim str As String

i = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

For i = i To 1 Step -1 'Loops backwards
    If Cells(i, 2).Value = 0 Then
        If Len(str) > 0 Then
            str = Cells(i, 1).Value & "|" & str
        Else
            str = Cells(i, 1).Value
        End If
        Else
            If Len(str) > 0 Then
                Cells(i + 1, 3).Value = Cells(i, 1).Value & "|" & str
                str = ""
            End If
    End If
Next

End Sub