Simple Macro to Auto fill Cells

[James]

Hi Everyone,

Hope you are all well


I have a very simple excel worksheets (Worksheet A and Worksheet B)
which has:

Column A: Empty
Column B: Has entry (First Name) on every cell till row 2000
Column C: Has entry (Last Name) on every cell till row 2000


I know how to get first and last name combined using
(=concatenate(B1," ",C1)) However I have no idea how I can make excel
do this in column A automatically upto row 2000 for worksheet A and B?

Can anyone please help?

Thank you for your help in advance.




James

 

[ryguy7272]

It's not elegant, but should do what you want:
Sub Concat()

Range("A2").Select
Dim redRng As Range

Set redRng = Range("A1", Range("A2000").End(xlUp))
For Each cell In redRng

If cell.Value <> "" Then
ActiveCell.Offset(0, 0).Select
ActiveCell.FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
ActiveCell.Offset(1, 0).Select
End If

Next cell

End Sub