Vba range modification

[SGT_Spittal]

I'm looking for a bit of help with the following code which I got from How to add a range of cells in excel – concat() | Chandoo.org - Learn Microsoft Excel Online

i want to modify this function based on the table below.

as you can see from the function you select a range and optional delimiter and it collates the range to one cell.

I want to modify this so that the selected range is bassed on the cell in column A

Example

In cell K2 I put the formulae =CONCAT(A2," ") but the formulae will look at the range C2:F4

[h=1]Function concat(useThis As Range, Optional delim As String) As String[/h]
<code style="margin: 0px; padding: 0px; border: none;">' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
if cstr(cell.value)<>"" and cstr(cell.value)<>" " then
retVal = retVal & cstr(cell.Value) & dlm
end if
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function


</code>

	A	B	C	D	E	F	G	H	I	J	K	L
1	Design Zone	Compartment No.	EQ's	CR's	EQ's 1	CR's 1
2	05DZ	766		200900,	201059,	201193,	201222,	=CONCAT(C2)	=CONCAT(G2:J4)
3	201237,	201253,	201280,	201323,
4	201360
5	05DZ	060	T26-DDI000059,	T26-EIS000543,	T26-EIS000741,	T26-EIS000743,	200521,	200669,	200741,	200745,	=CONCAT(C5:F18)	=CONCAT(G5:J18)
6	T26-EIS000833,	T26-EIS000966,	T26-EIS000976,	T26-EIS001302,	200787,	200789,	200824,	200857,
7	T26-EIS001314,	T26-EIS001315,	T26-EIS001316,	T26-EIS001318,	200870,	200898,	200900,	200917,
8	T26-EIS001357				200920,	200926,	200929,	200933,
9					200944,	200989,	200997,	201019,
10					201056,	201059,	201060,	201085,
11					201092,	201102,	201117,	201119,
12					201128,	201140,	201193,	201197,
13					201199,	201222,	201237,	201253,
14					201266,	201268,	201280,	201281,
15					201287,	201323,	201328,	201341,
16					201346,	201351,	201359,	201362,
17					201374,	201396,	201422,	201495,
18					201508,	201521,	201534
19	05DZ	061		200944,	201422
20	05DZ	063	T26-DDI000079,	T26-EIS000274,	T26-EIS000543,	T26-EIS000622,	200604,	200669,	200682,	200741,
21	T26-EIS000741,	T26-EIS000743,	T26-EIS000846,	T26-EIS000895,	200779,	200789,	200882,	200898,
22	T26-EIS000976				200905,	200922,	200939,	200944,
23					200997,	200998,	201004,	201019,
24					201054,	201060,	201085,	201092,
25					201113,	201117,	201119,	201139,
26					201140,	201161,	201165,	201174,
27					201193,	201237,	201266,	201280,
28					201323,	201336,	201351,	201361,
29					201410,	201508,	201521,	201532
30	05DZ	063	T26-DDI000079,	T26-EIS000274,	T26-EIS000543,	T26-EIS000622,	200604,	200669,	200682,	200741,
31	T26-EIS000741,	T26-EIS000743,	T26-EIS000846,	T26-EIS000895,	200779,	200789,	200882,	200898,
32	T26-EIS000976				200905,	200922,	200939,	200944,
33					200997,	200998,	201004,	201019,
34					201054,	201060,	201085,	201092,
35					201113,	201117,	201119,	201139,
36					201140,	201161,	201165,	201174,
37					201193,	201237,	201266,	201280,
38					201323,	201336,	201351,	201361,
39					201410,	201508,	201521,	201532

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: none;">
</code>

 

[SGT_Spittal]

	A	B	C	D	E	F	G	H	I	J	K	L
1	Design Zone	Compartment No.	EQ's	CR's	EQ's 1	CR's 1
2	05DZ	766		200900,	201059,	201193,	201222,	=CONCAT(C2:F4)	=CONCAT(G2:J4)
3	201237,	201253,	201280,	201323,
4	201360
5	05DZ	060	T26-DDI000059,	T26-EIS000543,	T26-EIS000741,	T26-EIS000743,	200521,	200669,	200741,	200745,	=CONCAT(C5:F18)	=CONCAT(G5:J18)
6	T26-EIS000833,	T26-EIS000966,	T26-EIS000976,	T26-EIS001302,	200787,	200789,	200824,	200857,
7	T26-EIS001314,	T26-EIS001315,	T26-EIS001316,	T26-EIS001318,	200870,	200898,	200900,	200917,
8	T26-EIS001357				200920,	200926,	200929,	200933,
9					200944,	200989,	200997,	201019,
10					201056,	201059,	201060,	201085,
11					201092,	201102,	201117,	201119,
12					201128,	201140,	201193,	201197,
13					201199,	201222,	201237,	201253,
14					201266,	201268,	201280,	201281,
15					201287,	201323,	201328,	201341,
16					201346,	201351,	201359,	201362,
17					201374,	201396,	201422,	201495,
18					201508,	201521,	201534
19	05DZ	061		200944,	201422
20	05DZ	063	T26-DDI000079,	T26-EIS000274,	T26-EIS000543,	T26-EIS000622,	200604,	200669,	200682,	200741,
21	T26-EIS000741,	T26-EIS000743,	T26-EIS000846,	T26-EIS000895,	200779,	200789,	200882,	200898,
22	T26-EIS000976				200905,	200922,	200939,	200944,
23					200997,	200998,	201004,	201019,
24					201054,	201060,	201085,	201092,
25					201113,	201117,	201119,	201139,
26					201140,	201161,	201165,	201174,
27					201193,	201237,	201266,	201280,
28					201323,	201336,	201351,	201361,
29					201410,	201508,	201521,	201532
30	05DZ	063	T26-DDI000079,	T26-EIS000274,	T26-EIS000543,	T26-EIS000622,	200604,	200669,	200682,	200741,
31	T26-EIS000741,	T26-EIS000743,	T26-EIS000846,	T26-EIS000895,	200779,	200789,	200882,	200898,
32	T26-EIS000976				200905,	200922,	200939,	200944,
33					200997,	200998,	201004,	201019,
34					201054,	201060,	201085,	201092,
35					201113,	201117,	201119,	201139,
36					201140,	201161,	201165,	201174,
37					201193,	201237,	201266,	201280,
38					201323,	201336,	201351,	201361,
39					201410,	201508,	201521,	201532

<colgroup><col><col span="12"></colgroup><tbody>
</tbody>