Solved: Excel 2003 - Is there a way I can speed up my AutoFill Formula for 15,000 rows?

[frank_m]

The bit of code below takes about 6 seconds to AutoFill a formula in Column N cells for 15,000 rows. In many cases the formula is already present in most or all of the cells; I'm only running it as a fail safe for users accidentally removing the formula in a couple of random cells. Is there anything I can do to speed up this process?

Thank you much
[vba]Dim LastRow As Long

LastRow = ActiveSheet.[B65536].End(xlUp).Row

Range("N16").FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"

Application.ScreenUpdating = False

Range("N16").AutoFill Range("N16:N" & LastRow - 1)

Application.ScreenUpdating = True[/vba]

[austenr]

try setting your calculations to manual.

[VBA]Application.Calculation = xlCalculationManual[/VBA]

[frank_m]

Thanks for the tip, but doing that is not making any noticeable difference.

[Bob Phillips]

Can you post the workbook Frank si that we can time it?

[Tinbendr]

How about this?
[vba]Sub AddFormula()
Dim RowRng() As Variant
Dim LastRow As Long
Dim A As Long
LastRow = ActiveSheet.[B65536].End(xlUp).Row + 1
ReDim RowRng(1 To LastRow, 1 To 1)
With ActiveSheet
For A = 1 To LastRow
RowRng(A, 1) = "=RC[-5]*RC[-2]+RC[-4]"
Next
.Cells(16, 14).Resize(LastRow - 16) = RowRng
End With
End Sub
[/vba]David

[jolivanes]

Another possibility.
Change cell references accordingly.

Enter your formula in A2.
Select A2 again, but make sure that you are not in Edit Mode.
On the left end of the formula bar you should see a box that says the address of the cell you are in (A2).
Click in that box and type A60000.
Hold down your Shift key as you hit Enter.
The range A2:A60000 should be selected.
Hit your F2 key.
Hit Ctrl+Enter.
This should have entered the same formula in A2:A60000. (I am assuming that for some reason you cannot just double-click on the auto-fill handle, which, of course, would be the easiest solution.)

[frank_m]

Sorry guys for wasting your time. I'm an idiot some times. I had forgotten that another sub was being called within the same sub that I was using AutoFill.
* When I dissabled that, the Autofill is lightening is fast.

For the heck of it I put a timer on a few different AutoFill pieces of code. Below are the results
[vba]
'15,000 Rows

Dim LastRow As Long

LastRow = ActiveSheet.[B65536].End(xlUp).Row

Application.ScreenUpdating = False

'====================================================
'Timer result was .145
Range("N16").FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"
Range("N16").AutoFill Range("N16:N" & LastRow - 1)

'====================================================
'Timer result was .082
Range("N16:N" & LastRow - 1).FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"

'====================================================
'Timer result was .176
Dim RowRng() As Variant
Dim A As Long

ReDim RowRng(1 To LastRow, 1 To 1)

With ActiveSheet
For A = 1 To LastRow
RowRng(A, 1) = "=RC[-5]*RC[-2]+RC[-4]"
Next
.Cells(16, 14).Resize(LastRow - 16) = RowRng
End With

Application.ScreenUpdating = True
[/vba]