PDA

View Full Version : Solved: AuotoFill is slower than using the Range. - Are there other factors to consider?



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

GTO
11-07-2011, 11:33 PM
Certainly more at a 'rookie' opinion, but FWIW, I believe that simply assigning a formula to the range is faster (as your code shows), because Excel is only changing one property of the cells. Autofill means Excel must look at the borders, patterns, etc., as well as adjust the formula.

I know of no downside to applying just the formula to the range, unless of course, you were needing to copy formatting etc.

frank_m
11-08-2011, 04:18 AM
HI Mark,

Thanks for the feed back. Makes a lot of sense.

By the way, you are an expert by my way of thinking. Not quite in the catagory of the great "Bob", and a select few others, but thats kind of like being good enough to be on the Pro golf tour, but not being compared to Tiger Woods. :yes

Aflatoon
11-08-2011, 05:45 AM
The main difference between the two, to my mind, is that if you have calculation set to manual, Autofill will not calculate each cell until you set calculation back to automatic, whereas using the Formula approach will calculate all of them regardless.
Depending on the situation, Autofill can therefore save you time.

frank_m
11-08-2011, 06:47 AM
Hats off to you Aflatoon

My complete routine is rather long and I tested your revelations with it and and I did confirm what you said, that using the range was slower than autofill. Even with calculations set to manual.

A little baffled as to why in the shorter routine that I posted here, the opposite was true.. but in the end, all that matters is what works best in the procedure I'm actually using

Thank you much for sharing your wisdom.

(Marking as solved)