Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
CurrVal = CurrVal + 1
Next CurrCol
Next CurrRow
‘ Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, “00.00”) & “ seconds”
End Sub
The example that follows demonstrates a much faster way to produce the same result. This code
inserts the values into an array and then uses a single statement to transfer the contents of an
array to the range.
Sub ArrayFillRange()
‘ Fill a range by transferring an array
Dim CellsDown As Long, CellsAcross As Integer
Dim i As Long, j As Integer
Dim StartTime As Double
Dim TempArray() As Long
Dim TheRange As Range
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
‘ Redimension temporary array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
‘ Set worksheet range
Set TheRange = ActiveCell.Range(Cells(1, 1), _
Cells(CellsDown, CellsAcross))
‘ Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal + 1
CurrVal = CurrVal + 1
Next j
Next i
‘ Transfer temporary array to worksheet
TheRange.Value = TempArray
‘ Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, “00.00”) & “ seconds”
End Sub
 
Search JabSto ::




Custom Search