Concat 2 columns then paste into a third

[Kriskros07]

Hi!

Following on from a previous thread, can someone tell me how i can concatenate 2 columns of data into 1 and then copy/paste the results into a third column.

I need to do this in VBA Excel.

Kind Regards,
Chris

 

[VoG]

Try like this

Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("C1:C" & LR)
    .Formula = "=A1&B1"
    .Value = .Value
End With
End Sub

 

[Kriskros07]

This is brilliant!

However, hoping not to sound unappreciative, is there any way of representing the column range as an indexed number, e.g where C1:C could be represented with a 3, instead. The issue is that the column i wish to paste into is likely to change however i can work out the relative position this range should be in given some other conditions. Alternatively if i could convert a number into a range of course...

Any further help very much appreciated.

 

[VoG]

Maybe like this?

Sub concat()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range(Cells(1, 3), Cells(LR, 3))
    .Formula = "=A1&B1"
    .Value = .Value
End With
End Sub