Change Rng.FormulaR1C1 = "=CONCATENATE(RC[-7],C[-6])" to

Rng.FormulaR1C1 = "=CONCATENATE(RC[-7],RC[-6])"

or

Rng.FormulaR1C1 = "=RC[-7]" & "&" & "RC[-6]"

Regards,

Hutch

"markx" wrote:

Wow! Quite efficient (took less than 1 second, I think)! Thanks a lot
Hutch!!!

One other question:
I realised that the formula I want to put in this column should be
different:
with "=CONCATENATE(C[-7],C[-6])" I get =CONCATENATE(G:G;H:H), but in fact I
looked for =CONCATENATE(G2;H2) (or even better =G2&H2) for row 2 and so
on...
The result is the same, but any idea how to modify the R1C1 formula to get
the second option?

Thanks once again,
Mark


"Tom Hutchins" wrote in message
...
Try this:

Sub Test()
Dim x As Long, Rng As Range
x& = Range("L" & Rows.Count).End(xlUp).Row
Set Rng = Range("N2:N" & x&)
Rng.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
Set Rng = Nothing
End Sub

Hope this helps,

Hutch

"markx" wrote:

Hello,
I have the following macro:
------------------------
Sub Test()
Range("N2").Select
Do
ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-7],C[-6])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
------------------------
It works fine, the only problem is that it takes time (20 seconds to
treat
1000 rows).
Are you aware of any more efficient way of writing this type of code?
Thanks for any hints!
Regards,
Mark