counting from 1 to 100,000,000 within 30 seconds,

Sub TESTa()
Dim i As Long
Dim t As Single
t = Timer

Application.ScreenUpdating = False
For i = 1 To 100000000
ActiveCell.FormulaR1C1 = i
Next
MsgBox Timer - t & "seconds"
End Sub

this is a VBA that counts from 1 to 100000000. but really slowly... im not sure how many seconds this takes but tried with 10,000,000 and this took about 4 mins.

is there any way of making this happen in 30 seconds or so?

Timer

Hi,

This code should take about 3 secs to complete

Sub TESTa()
t = Timer
For i = 1 To 100000000
Next
MsgBox Timer - t & "seconds"
End Sub

Hope this helps

Nick's picture

yep... turn calculation to

yep... turn calculation to manual at the start, and disable events

application.calculation = xlcalculationmanual
application.enableevents=false

... then turn them back on at the end

take a look here too:
http://excelexperts.com/VBA-Tips-ScreenUpdating-Make-code-run-faster