Cocatenating two columns of text into a third

[petergr]

This should be easy, I have a column of First Names, a column of second names and I wish to create a new column with First Name , space, Last name.

I can create a cell specific code such as
= CONCATENATE(B2," ",C2)

However I want to make this into a macro to run on any file that I open for every entry in the columns (I export a file weekly and need to run it each time).

My macro and VBA skills are very basic!

Thanks for your help.

 

[VoG]

Assuming always concatenate columns B and C with results in column D, try

Sub concat()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("D2:D" & LR).Formula = "= CONCATENATE(B2,"" "",C2)"
End Sub

 

[JackDanIce]

Welcome to the board.

Assuming your two columns are A and B, that row 1 is the header row (so your data starts in row 2) and you want to concatenate into C, try:

Sub Concatenate()
Dim i as Long
 
Application.ScreenUpdating = False
 
i = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & i).Formula = "=A2" & "" "" & "B2"
 
Application.ScreenUpdating = True
 
End Sub

 

[petergr]

Thanks for your help, I will try this tonight. Thanks for being very specific with the references - it is a big help to newbies like me!

Peter

 

[petergr]

Re: Concatenating two columns of text into a third

Thanks VoG, unfortunately your code produced an error in each cell and I wasnt clever enough to debug it. JackDanIce your code worked straight away.

Thanks for helping me out.

Peter

 

[JackDanIce]

Only difference between VoG's code and mine are the columns we reference - mine looks at A and B to create your output in C, VoG's looks at B and C to place output into D. The letters in red refer to column letters:

Sub Concatenate()
Dim i as Long
 
Application.ScreenUpdating = False
 
' This line finds the last row in column A that has a value in it
i = Range("A" & Rows.Count).End(xlUp).Row
 
' This line is put a formula into every row in column C between rows 2 and
' The last used row that we found in column A
' The formula combines what is in column A with a space and then what is in column B
' Using "&" is another way of saying "Concatenate
' In VBA code, spaces have to be surrounded by a pair of speach marks
Range("C2:C" & i).Formula = "=A2" & "" "" & "B2"
 
Application.ScreenUpdating = True
 
End Sub

 

[petergr]

Re: Concatenating two columns of text into a third

Sorry JackDanIce I did transpose the approprate columns, but I am sure it was my error somewhere! As I said, my skills are pretty basic.

Thanks again

 

[JackDanIce]

No worries, was just to help you understand my code and show VoG had his correct too - it works now anyway!