Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
This simple routine has crept into my code and I use it thoughtlessly and
often: For i = 1 To 50000 ' some seriously large value here! Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value Next i I discovered it is a time-consumer, searched for alternatives and found several, but am just not VBA fluent enough to judge their efficiency. Any more experienced programmers can help me find the truly fastest solution? Thanks! -Joni |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
One way:
With Range("C1:C50000") .Formula = "=A1+B1" .Value = .Value End With In article , jonigr wrote: This simple routine has crept into my code and I use it thoughtlessly and often: For i = 1 To 50000 ' some seriously large value here! Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value Next i I discovered it is a time-consumer, searched for alternatives and found several, but am just not VBA fluent enough to judge their efficiency. Any more experienced programmers can help me find the truly fastest solution? Thanks! -Joni |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
Another:
Const RowCount As Long = 50000 Sub Alt2() Dim Arr1 As Variant Dim Arr2 As Variant Dim Counter As Long Arr1 = Range("A1").Resize(RowCount).Value Arr2 = Range("B1").Resize(RowCount).Value For Counter = 1 To RowCount Arr1(Counter, 1) = Arr1(Counter, 1) + Arr2(Counter, 1) Next Range("C1").Resize(RowCount).Value = Arr1 End Sub -- Jim "jonigr" wrote in message ... | This simple routine has crept into my code and I use it thoughtlessly and | often: | | For i = 1 To 50000 ' some seriously large value here! | Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value | Next i | | I discovered it is a time-consumer, searched for alternatives and found | several, but am just not VBA fluent enough to judge their efficiency. | | Any more experienced programmers can help me find the truly fastest solution? | | Thanks! | | -Joni |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
I am delighted!
Jim's slightly more complex code (I'm still deciphering it) is actually the winner with an exec. time of... 0.11 sec. JE is close 2nd with 0.14 sec. But compared to my monstrous... 5.5 sec... both are winners: over 40x faster! I expected improvement, but not that much, so I wonder: is there a good resource dedicated to VBA *efficiency*: speeding up existing routines etc.? Thanks very much! -Joni "Jim Rech" wrote: Another: Const RowCount As Long = 50000 Sub Alt2() Dim Arr1 As Variant Dim Arr2 As Variant Dim Counter As Long Arr1 = Range("A1").Resize(RowCount).Value Arr2 = Range("B1").Resize(RowCount).Value For Counter = 1 To RowCount Arr1(Counter, 1) = Arr1(Counter, 1) + Arr2(Counter, 1) Next Range("C1").Resize(RowCount).Value = Arr1 End Sub -- Jim "jonigr" wrote in message ... | This simple routine has crept into my code and I use it thoughtlessly and | often: | | For i = 1 To 50000 ' some seriously large value here! | Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value | Next i | | I discovered it is a time-consumer, searched for alternatives and found | several, but am just not VBA fluent enough to judge their efficiency. | | Any more experienced programmers can help me find the truly fastest solution? | | Thanks! | | -Joni |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
i like to use what I call a poor-man's version of list processing...
[C1:C50000] = [A1:A50000+B1:B50000] HTH Dana DeLouis jonigr wrote: This simple routine has crept into my code and I use it thoughtlessly and often: For i = 1 To 50000 ' some seriously large value here! Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value Next i I discovered it is a time-consumer, searched for alternatives and found several, but am just not VBA fluent enough to judge their efficiency. Any more experienced programmers can help me find the truly fastest solution? Thanks! -Joni |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
most efficient large range summing?
Woooh, an absolute winner with 0.06 sec!
I had to look up the use of the square brackets, never seen that before. Was curious to see if a Range version of this works as well, like: Range("C1", "C50000") = Range("A1", "A50000") + Range("B1", "B50000") but obviously that doesn't. My VBA manual (or VB Help) doesn't seem to mention this list processing method at all. Any documentation you can point me to? Thanks! -Joni "Dana DeLouis" wrote: i like to use what I call a poor-man's version of list processing... [C1:C50000] = [A1:A50000+B1:B50000] HTH Dana DeLouis jonigr wrote: This simple routine has crept into my code and I use it thoughtlessly and often: For i = 1 To 50000 ' some seriously large value here! Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value Next i I discovered it is a time-consumer, searched for alternatives and found several, but am just not VBA fluent enough to judge their efficiency. Any more experienced programmers can help me find the truly fastest solution? Thanks! -Joni |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large Time totals not summing. | Excel Worksheet Functions | |||
Large Time values not summing | Excel Worksheet Functions | |||
more efficient way to lookup a range? | Excel Discussion (Misc queries) | |||
Summing large numbers... | Excel Worksheet Functions | |||
Summing large columns | Excel Worksheet Functions |