Vba to concatenate 3 columns

[RAM1972]

Hi All

Lookinga vba code to concatenate 3 columns irespective of data entered with results in columns columns d

[TABLE="width: 520"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Tariff No
[/TD]
[TD]Description[/TD]
[TD]Origin
[/TD]
[TD]Tarif No Description Origin
[/TD]
[/TR]
[TR]
[TD="align: right"]12345678[/TD]
[TD]MAZDA[/TD]
[TD]AE[/TD]
[TD]12345678 MAZDA AE[/TD]
[/TR]
[TR]
[TD="align: right"]5458789[/TD]
[TD]FORD[/TD]
[TD]AE[/TD]
[TD]5458789 FORD AE[/TD]
[/TR]
[TR]
[TD]21411ASDD[/TD]
[TD]TOYOTA[/TD]
[TD]AE[/TD]
[TD]21411ASDD TOYOTA AE
[/TD]
[/TR]
[TR]
[TD]DEDED[/TD]
[TD]BEDFORD[/TD]
[TD]AE[/TD]
[TD]DEDED BEDFORD AE[/TD]
[/TR]
[TR]
[TD]EDEFER[/TD]
[TD]HUMMER[/TD]
[TD]AT[/TD]
[TD]EDEFER HUMMER AT[/TD]
[/TR]
[TR]
[TD]FRRG[/TD]
[TD]RENAULT[/TD]
[TD]CN[/TD]
[TD]FRRG RENAULT CN
[/TD]
[/TR]
[TR]
[TD]FRFR[/TD]
[TD]PEUGEOT[/TD]
[TD]CN[/TD]
[TD]FRFR PEUGEOT CN[/TD]
[/TR]
[TR]
[TD]RRRR[/TD]
[TD]CRUISER MODEL[/TD]
[TD]CN[/TD]
[TD]RRRR CRUISER MODEL CN[/TD]
[/TR]
[TR]
[TD]FRRT[/TD]
[TD]MINI COOPER [/TD]
[TD]CN[/TD]
[TD]FRRT MINI COOPER CN[/TD]
[/TR]
</tbody>[/TABLE]

 

[JackDanIce]

The code below assumes you have headers in row1 and you want a space in between each value (as your example for column D suggests):

Sub Concatenate3Cols()

    Dim arr()       As Variant
    Dim x           As Long
    Const delim     As String = " "

    x = Cells(rows.count, 1).End(xlUp).row
    arr = Cells(2, 1).Resize(x - 1, 4).value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        arr(x, 4) = arr(x, 1) & delim & arr(x, 2) & delim & arr(x, 3)
    Next x
    
    Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).value = arr
    
    Erase arr
    
End Sub

 

[Rick Rothstein]

Here is another macro to consider...

Sub ConcatColumnsABC()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("D1:D" & LastRow) = Evaluate(Replace("A1:A#&"" ""&B1:B#&"" ""&C1:C#", "#", LastRow))
End Sub

While my code measures twice as fast as JackDanIce's code, you will never notice the physical difference, hence, the choice is yours... for 10,000 rows of data, my code took 0.03 seconds and JackDanIce's code took, 0.06 seconds.

 

[RAM1972]

thanks for help working

 

[RAM1972]

thanks workings, however can u explain a sample to me how to measure the speed a formula works.
Do agree it works very fast

 

[Rick Rothstein]

thanks workings, however can u explain a sample to me how to measure the speed a formula works.

Sub YouMacro()
  Dim AnyVariableHere
  [B][COLOR="#FF0000"]Debug.Print "?-" & Timer & "+";[/COLOR][/B]
  {Your actual code goes here}
  [B][COLOR="#FF0000"]Debug.Print Timer[/COLOR][/B]
End Sub

After you run your code, click in the line that the above code just printed and hit the Enter key... the displayed answer is in seconds... usually the number of seconds is small, so make sure to look for the exponential power of 10 at the end, for example, if this is the printout...

6.00000000049477E-02

The E-02 part means move the decimal point 2 places to the left, so the value becomes (when rounded)...

0.06

 

[RAM1972]

Thanks Rick for this info

I learn a lot and this technique rocks