Concatenate multiple rows then move to next column

[pbt]

I'm having trouble with this. I've tried searching but can't find anything that helps.

This is the current code I have:

With Sheets("Crew 4")
        ' determine the end-of-data col
        i = .Cells(4, .Columns.Count).End(xlToLeft).Column
        If i < 2 Then
            MsgBox "No data on CREW sheet."
            GoTo Xit
        End If
        ' insert a temporary column
        .Columns("A:A").Insert Shift:=xlToRight
        
        With .Range("A4:A" & i)
            ' seed formula in A4-down than concatenate the Job+Lot+(Phase+Code)
            .FormulaR1C1 = "=R4C4&"":""&R5C4&"":""&R7C4&R8C4"

This is my Data layout:

Excel 2010
	B	D	E	F	G
4	Job Number	DRRC15013	DRRC15013	DRRC15013	DRRC15013
5	Lot #	31	31	31	31
6	Model / Elev.	1839	1839	1839	1839
7	Phase	SC	LH	LH	LH
8	Code	10	10	30	80
9	Budget	18.00	631.00	203.00	31.00
Crew 4

What I am trying to do, starting in Col D, Concat. Rows 4, 5,7, & 8 into the newly inserted Col A4 then move down to A5 and do the same thing with Col E, etc. etc. to the end of the Variable i which was set to Col 17 prior to the insertion of row A.

This is the results I get currently in Col A:

Excel 2010
	A
4	DRRC15013:31:SC10
5	DRRC15013:31:SC10
6	DRRC15013:31:SC10
7	DRRC15013:31:SC10
Crew 4

It gives the same value as col D, as you can see.
I'm only showing four rows but Col A has only 14 entries all the same, not 17 entries.

I need help so A5 reads Col E, A6 reads Col F, etc.

(Col C hidden sensitive info)

Thanks in advance.

 

[pbt]

Thank you very much.

Makes sense.

Have a Great Day