Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
WriteTime = Format(Timer - Time1, “00:00”)
‘ Read the range into the array
Time1 = Timer
For i = 1 To NumElements
MyArray(i) = Cells(i, 1)
Next i
ReadTime = Format(Timer - Time1, “00:00”)
‘ Show results
Msg = “Write: “ & WriteTime
Msg = Msg & vbCrLf
Msg = Msg & “Read: “ & ReadTime
MsgBox Msg, vbOKOnly, NumElements & “ Elements”
End Sub
On my system, it took 58 seconds to write a 60,000-element array to a range, but it took less
than 1 second to read the range into an array.
A better way to write to a range
The example in the preceding section uses a For-Next loop to transfer the contents of an array
to a worksheet range. In this section, I demonstrate a more efficient way to accomplish this.
Start with the example that follows, which illustrates the most obvious (but not the most
efficient) way to fill a range. This example uses a For-Next loop to insert its values in a range.
Sub LoopFillRange()
‘ Fill a range by looping through cells
Dim CellsDown As Long, CellsAcross As Integer
Dim CurrRow As Long, CurrCol As Integer
Dim StartTime As Double
Dim CurrVal As Long
‘ Get the dimensions
CellsDown = InputBox(“How many cells down?”)
If CellsDown = 0 Then Exit Sub
CellsAcross = InputBox(“How many cells across?”)
If CellsAcross = 0 Then Exit Sub
‘ Record starting time
StartTime = Timer
‘ Loop through cells and insert values
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
ActiveCell.Offset(CurrRow - 1, _
CurrCol - 1).Value = CurrVal
Search JabSto ::

Custom Search