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
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