# What is the efficient way to join array?

##### Board Regular
Hello,

I have two column A and B and want to join their value and put in column 3.

I am trying to follow this algorithm for better performance.
array1 = value from column A => {1,2,3,4,5}
array2 = value from column B => {EA, FT, IN, EA, FT}

array3 = 1 EA, 2 FT, 3 IN, 4 EA, 5 FT

then
column 3 value is the value of column3

Any help how to implement in VBA or any better way, please!

Thank you

#### Fluff

##### MrExcel MVP, Moderator
If you are just trying to concatenate all the values in Col A with the values in col B. Then try
Code:
``````Sub ConcatArry()
Dim ary As Variant
Dim St As String
Dim i As Long
ary = Range("A1").CurrentRegion.value2
For i = 1 To UBound(ary)
St = St & ary(i, 1) & " " & ary(i, 2) & " "
Next i
Range("C1").Value = Left(St, Len(St) - 1)
End Sub``````

#### Rick Rothstein

##### MrExcel MVP
If you are just trying to concatenate all the values in Col A with the values in col B. Then try
Code:
``````Sub ConcatArry()
Dim ary As Variant
Dim St As String
Dim i As Long
ary = Range("A1").CurrentRegion.value2
For i = 1 To UBound(ary)
St = St & ary(i, 1) & " " & ary(i, 2) & " "
Next i
Range("C1").Value = Left(St, Len(St) - 1)
End Sub``````
I am not 100% sure that is what the OP is asking for, but if it turns out to be what he wants, here is another way to write your code...
Code:
``````[table="width: 500"]
[tr]
[td]Sub ConcatArry()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1") = Join(Application.Transpose(Evaluate(Replace("A1:A#&"" ""&B1:B#", "#", LastRow))))
End Sub[/td]
[/tr]
[/table]``````

• Fluff

#### Fluff

##### MrExcel MVP, Moderator
I am not 100% sure that is what the OP is asking for
Nor am I. ##### Board Regular
Thank you both for the response. Both codes put the concatenated value on C1.
I am expecting
C1 = 1 EA
C2 = 2 FT
C3 = 3 IN

And so on...........

#### Rick Rothstein

##### MrExcel MVP
Thank you both for the response. Both codes put the concatenated value on C1.
I am expecting
C1 = 1 EA
C2 = 2 FT
C3 = 3 IN

And so on...........
Give this code a try...
Code:
``````[table="width: 500"]
[tr]
[td]Sub ConcatArry()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1:C" & LastRow) = Evaluate(Replace("A1:A#&"" ""&B1:B#", "#", LastRow))
End Sub[/td]
[/tr]
[/table]``````

• #### Fluff

##### MrExcel MVP, Moderator
Or with my version
Code:
``````Sub ConcatArry()
Dim ary As Variant
Dim i As Long
ary = Range("A1", Range("A" & Rows.count).End(xlUp).Offset(, 2)).value2
For i = 1 To UBound(ary)
ary(i, 3) = ary(i, 1) & " " & ary(i, 2)
Next i
Range("C1").Resize(UBound(ary)).Value = Application.Index(ary, 0, 3)
End Sub``````

• ##### Board Regular
Thank you very much Fluff and Rick for making it work.

#### Rick Rothstein

##### MrExcel MVP
Or with my version
I thought you liked my code? Code:
``````Sub ConcatArry()
Dim ary As Variant
Dim i As Long
ary = Range("A1", Range("A" & Rows.count).End(xlUp).Offset(, 2)).value2
For i = 1 To UBound(ary)
ary(i, 3) = ary(i, 1) & " " & ary(i, 2)
Next i
Range("C1").Resize(UBound(ary)).Value = Application.Index(ary, 0, 3)
End Sub``````
You can simplify your code slightly (removes the Index function call) by assigning your concatenations back to the first array index on each loop and then simply assigning the array to the output column like this...
Code:
``````Sub ConcatArry()
Dim ary As Variant
Dim i As Long
ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, [B][COLOR="#FF0000"]1[/COLOR][/B])).Value2
For i = 1 To UBound(ary)
ary(i, [B][COLOR="#FF0000"]1[/COLOR][/B]) = ary(i, 1) & " " & ary(i, 2)
Next i
Range("C1").Resize(UBound(ary)).Value = [B][COLOR="#FF0000"]ary[/COLOR][/B]
End Sub``````

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
I thought you liked my code? I do, just thought I'd give the OP an option (added to which I'm bored) 