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

Tariff No	Description	Origin	Tarif No Description Origin
12345678	MAZDA	AE	12345678 MAZDA AE
5458789	FORD	AE	5458789 FORD AE
21411ASDD	TOYOTA	AE	21411ASDD TOYOTA AE
DEDED	BEDFORD	AE	DEDED BEDFORD AE
EDEFER	HUMMER	AT	EDEFER HUMMER AT
FRRG	RENAULT	CN	FRRG RENAULT CN
FRFR	PEUGEOT	CN	FRFR PEUGEOT CN
RRRR	CRUISER MODEL	CN	RRRR CRUISER MODEL CN
FRRT	MINI COOPER	CN	FRRT MINI COOPER CN

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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