What is the efficient way to join array?

[dmadhup]

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]

If you are just trying to concatenate all the values in Col A with the values in col B. Then try

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]

If you are just trying to concatenate all the values in Col A with the values in col B. Then try

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...

[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]

I am not 100% sure that is what the OP is asking for

Nor am I.
But I like your version.

 

[dmadhup]

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]

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...

[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]

Or with my version

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

 

[dmadhup]

Thank you very much Fluff and Rick for making it work.

 

[Rick Rothstein]

Or with my version

I thought you liked my 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...

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

 

[Fluff]

I thought you liked my code?

I do, just thought I'd give the OP an option (added to which I'm bored)