Array to speed up (Range vs Array)
Below two procedures are different ways of populating a range of cells. The one at the bottom using arrays is the faster one. We can use the same to do processing/calculations in arrays and then put the calculated data back to range to speed up the process. Copy these procedures in a general module and run them (F5).
Sub PopulatingRangeEachCell() 'This procedure populates the range one cell at time Dim rng As Range Dim rngCell As Range Dim lngCnt As Long Dim stTime As Date stTime = Now Set rng = Sheet1.Range("A1:A100000") For Each rngCell In rng lngCnt = lngCnt + 1 rngCell.Value = lngCnt Next rngCell Set rng = Nothing Set rngCell = Nothing MsgBox "Start Time: " & Format(stTime, "hh:mm:ss") & vbCrLf & _ "End Time: " & Format(Now, "hh:mm:ss") & vbCrLf & _ DateDiff("s", stTime, Now) & " seconds", vbInformation, "Time Taken" End Sub Sub PopulatingRangeUsingArray() 'This procedure assigns range to array 'Then populates array and 'then assign array back to range Dim rng As Range Dim lngCnt As Long Dim stTime As Date Dim arr stTime = Now Set rng = Sheet1.Range("A1:A100000") arr = rng For lngCnt = LBound(arr, 1) To UBound(arr, 1) arr(lngCnt, 1) = lngCnt Next lngCnt rng = arr Erase arr Set rng = Nothing MsgBox "Start Time: " & Format(stTime, "hh:mm:ss") & vbCrLf & _ "End Time: " & Format(Now, "hh:mm:ss") & vbCrLf & _ DateDiff("s", stTime, Now) & " seconds", vbInformation, "Time Taken" End Sub
»
- Vishesh's blog
- Login or register to post comments
- 14317 reads
Great job! Can you post code to include more columns?
Hi Vishesh, great job!
Can you ammend code to include more columns?
I would like to select a range of cells with heavy formula/calculations and calculate them faster than now.
Thanks in advance.