frank_m
11-07-2011, 09:52 PM
I've been using this bit of code below for quite awhile that usues AutoFill for filling a 20,000 row column.
Right now I found that using the range is faster.
0.099 verses 0.825
Before I adopt this into my frequently used procedure I thought it best to ask the experts here if they see any pitfalls with that.
(By the way, Thanks Paul. I barrowed your timer that I saw you use here today)
Sub AutoFill_Formula()
Dim LastRow As Long
Dim t1 As Single, t2 As Single
t1 = Timer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With
Range("N16").FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"
Range("N16").AutoFill Range("N16:N" & LastRow)
t2 = Timer
MsgBox (t2 - t1) 'approx 0.825 seconds
End Sub
Sub Range_Equals_Formula()
Dim LastRow As Long
Dim t1 As Single, t2 As Single
Dim rData As Range
t1 = Timer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With
Set rData = Range("N16:N" & LastRow)
rData.FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"
t2 = Timer
MsgBox (t2 - t1) 'approx 0.099 seconds
End Sub
Right now I found that using the range is faster.
0.099 verses 0.825
Before I adopt this into my frequently used procedure I thought it best to ask the experts here if they see any pitfalls with that.
(By the way, Thanks Paul. I barrowed your timer that I saw you use here today)
Sub AutoFill_Formula()
Dim LastRow As Long
Dim t1 As Single, t2 As Single
t1 = Timer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With
Range("N16").FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"
Range("N16").AutoFill Range("N16:N" & LastRow)
t2 = Timer
MsgBox (t2 - t1) 'approx 0.825 seconds
End Sub
Sub Range_Equals_Formula()
Dim LastRow As Long
Dim t1 As Single, t2 As Single
Dim rData As Range
t1 = Timer
With ActiveSheet
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With
Set rData = Range("N16:N" & LastRow)
rData.FormulaR1C1 = "=RC[-5]*RC[-2]+RC[-4]"
t2 = Timer
MsgBox (t2 - t1) 'approx 0.099 seconds
End Sub