more efficient VBA?
 

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

more efficient VBA?
 

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

more efficient VBA?
 

Hi Mark

Don't select and activate stuff, it's slow and unnecessary.
Also, if you have formulae there, they will recalculate on every cell entry,
it takes time. Turn calculation temporarily off while writing things to
multiple cells.

Try this, it is be pretty fast:

Sub test()
Dim R As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For R = 2 To Cells(Rows.Count, 12).End(xlUp).Row
Cells(R, 14).FormulaR1C1 = _
"=CONCATENATE(C[-7],C[-6])"
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

HTH. Best wishes Harald


"markx" skrev i melding
...
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

more efficient VBA?
 

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

more efficient VBA?
 

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