Beatrix
05-25-2010, 05:08 AM
Dear All ,
I need to find a VBA code to join texts from 2 columns into 1. In the spreadsheet I have a long list, column E has Surname info and Column D has firstname info.. I need to join them in column B by putting a space between Surname and Firstname.
Excel Concatenate function works for this case but I need to do it by using VBA code. So far , I've found a code and it worked however I don't know how to put a space between RC5 and RC4 without including another column..
Your help would be much appreciated..
Sub Macro9()
Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Range("AA1:AA" & LR).FormulaR1C1 = "=(RC5 & RC4)"
Range("AA1:AA" & LR).Value = Range("AA1:AA" & LR).Value
Range("AA1:AA" & LR).Copy
Range("B1").PasteSpecial xlPasteValues
Range("AA1:AA" & LR).Clear
End Sub
I need to find a VBA code to join texts from 2 columns into 1. In the spreadsheet I have a long list, column E has Surname info and Column D has firstname info.. I need to join them in column B by putting a space between Surname and Firstname.
Excel Concatenate function works for this case but I need to do it by using VBA code. So far , I've found a code and it worked however I don't know how to put a space between RC5 and RC4 without including another column..
Your help would be much appreciated..
Sub Macro9()
Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
Range("AA1:AA" & LR).FormulaR1C1 = "=(RC5 & RC4)"
Range("AA1:AA" & LR).Value = Range("AA1:AA" & LR).Value
Range("AA1:AA" & LR).Copy
Range("B1").PasteSpecial xlPasteValues
Range("AA1:AA" & LR).Clear
End Sub