Simple Macro to Auto fill Cells

J

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top