PDA

View Full Version : Solved: Handling large ranges



mdmackillop
04-21-2009, 04:06 PM
I came across this article the other day which might be of interest
http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx

I made use of the technique in trimming 8 columns x 300,000 rows of text from a database output in 12.3 seconds.

Two simple versions to test


Sub Quick()
Dim Rng As Range
Dim arr As Variant
Dim i As Long, j As Long
Dim Tim As Single

Cells.Clear
Tim = Timer
Set Rng = Range(Cells(1, 1), Cells(5000, 20))
arr = Rng
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
arr(i, j) = i + j
Next
Next
Rng = arr
Cells(1, 1) = Timer - Tim
End Sub

Sub Slow()
Dim i As Long, j As Long
Dim Tim As Single

Cells.Clear
Application.ScreenUpdating = False
Tim = Timer
For i = 1 To 5000
For j = 1 To 20
Cells(i, j) = i + j
Next
Next
Application.ScreenUpdating = True
Cells(1, 1) = Timer - Tim
End Sub

Dave
04-22-2009, 03:11 PM
This is even faster. The "Slow" sub is considerably faster. Dave

Sub Quick()
Dim Rng As Range
Dim arr As Variant
Dim i As Long, j As Long
Dim Tim As Single
Cells.Clear
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Tim = Timer
Set Rng = Range(Cells(1, 1), Cells(5000, 20))
arr = Rng
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
arr(i, j) = i + j
Next
Next
Rng = arr
Cells(1, 1) = Timer - Tim
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Sub Slow()
Dim i As Long, j As Long
Dim Tim As Single
Cells.Clear
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Tim = Timer
For i = 1 To 5000
For j = 1 To 20
Cells(i, j) = i + j
Next
Next
Cells(1, 1) = Timer - Tim
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub