Inserting formula into macro

[John T]

Hi, I have the following formula in cell N2

=I2&(" ")&J2&(" ")&K2


I2 Is a persons title
J2 is a persons initial
K2 is a persons surname

My formula combines them into one cell.
Is there a code that will type this in cell N2 and copy it down to the last row with data?
Ideally i then want columns I, J and K replaced with the data in column N. (usually i would paste special and delete columns I, J & K then insert column N into I).

Thanks

 

[anand3dinesh]

how about this

Sub test()

Dim i As Long, Lrow As Long
Lrow = Range("I1").CurrentRegion.Rows.Count

For i = 2 To Lrow
Cells(i, 14).Value = Cells(i, 9) & " " & Cells(i, 10) & " " & Cells(i, 11)
Next i
End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[John T]

Thanks, that formula worked perfectly.

 

[Fluff]

As you are using 365, you could also use

Cells(i, 14).Value = Application.TextJoin(" ", , Cells(i, 9).Resize(, 3))