Quickest way to concatenate multiple columns on multiple rows

[Philippe.T]

Hello all,

I've got thousands of rows for which I need to concatenate multiple columns like in below example.
In belwo example the columns B,C and D are concatenated in column E

My question is: what is the quickest way?

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version, you could use TEXTJOIN

 

[Philippe.T]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version, you could use TEXTJOIN

Hello Fluff,

I updated my account details.
Note that I am looking for a VBA solution and not for a formula

 

[Fluff]

Roughly how many rows is thousands?

 

[Philippe.T]

Roughly how many rows is thousands?

It changes. Most of the times around 6000, but can be more than 40000

 

[Fluff]

Ok, how about

Sub PhilippeT()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   Ary = Range("B2:D" & Range("B" & Rows.Count).End(xlUp).Row)
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   
   For r = 1 To UBound(Ary)
      Nary(r, 1) = Ary(r, 1) & ";" & Ary(r, 2) & ";" & Ary(r, 3)
   Next r
   Range("E2").Resize(r - 1).Value = Nary
End Sub

 

[Philippe.T]

Ok, how about

Sub PhilippeT()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
  
   Ary = Range("B2:D" & Range("B" & Rows.Count).End(xlUp).Row)
   ReDim Nary(1 To UBound(Ary), 1 To 1)
  
   For r = 1 To UBound(Ary)
      Nary(r, 1) = Ary(r, 1) & ";" & Ary(r, 2) & ";" & Ary(r, 3)
   Next r
   Range("E2").Resize(r - 1).Value = Nary
End Sub

Many Thanks Fluff. This works great!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[lrobbo314]

Playing around with this I realized that on 400k+ rows, arraylists are slow. Dictionaries are better, but just a good old array like Fluff used was lightning quick.

This might shave off a millisecond or two, but for all intents and purposes, they are the same speed. Fun to play with though.

Sub COMBIX()
Dim a As Range:         Set a = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

a.Offset(, 3).Value = Evaluate(Replace(Replace(Replace("=@&"";""&*&"";""&#", "@", a.Address), "*", a.Offset(, 1).Address), "#", a.Offset(, 2).Address))

End Sub