What is the efficient way to join array?

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
95
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
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,394
Office Version
2010
Platform
Windows
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]
 

dmadhup

Board Regular
Joined
Feb 21, 2018
Messages
95
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
Joined
Apr 18, 2011
Messages
35,394
Office Version
2010
Platform
Windows
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
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,394
Office Version
2010
Platform
Windows
Or with my version
I thought you liked my code? :devilish:



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:

Forum statistics

Threads
1,077,639
Messages
5,335,400
Members
399,015
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top