Solved: Handling large ranges

[mdmackillop]

I came across this article the other day which might be of interest
http://blogs.msdn.com/excel/archive/...-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

[VBA]
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

[/VBA]

[Dave]

This is even faster. The "Slow" sub is considerably faster. Dave
[VBA]
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
[/VBA]