Excel: concatenate loop to end of row

Hello I am pretty new to VBA programing and I am stuck on a Concatenate problem. I have a .txt document (outputted from a XML program) that I have to upload into excel and manually format to meet our reporting standards (outside reporting agency) anyway, I have set up a Sub to separate the data into useful chunks

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim iLastRow As Long
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Evaluate("SUMPRODUCT(COUNTIF(" & .Cells(i, TEST_COLUMN).Address & _
",{""*COMPLETED*"",""*Expires*""}))") > 0 Then
Rows(i).Offset(1).EntireRow.Insert xlShiftDown
End If
Next i
End With
End Sub
But now I am trying to Concatenate the data from the 4 to 5 cell "chunks" to 1 cell that contains all the data. for example Colum A colum A (on separete sheet) Partial data 1 Complete data 1, 2, 3, & 4 Partial data 2 Complete data 1, 2, 3, & 4 Partial data 3 Partial data 4 Blank row Partial data 1 ect I am trying to make a looping Concatenate function but I cannot get it to work. I correctly concatenates the first set of data but then stops at the blank row. Is there a way to have it continue to loop? Here is what I have so far

Public Function ColConc(CellRef As Range, Delimiter As String)
Dim LoopVar As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Concat As String
Dim Col As Long
With Cells.SpecialCells(xlCellTypeConstants)
Col = CellRef.Column
StartRow = CellRef.Row
EndRow = CellRef.End(xlDown).Row
Concat = ""
For LoopVar = StartRow To EndRow
Concat = Concat & Cells(LoopVar, Col).Value
If LoopVar <> EndRow Then Concat = Concat & Delimiter
End With
ColConc = Concat
End Function
I thought the LoopVAr would have the sub continue on to the next set but it does not so I am lost. thanks for any help in advance

This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of May 26, 2015.

For more resources for Microsoft Excel