most efficient large range summing?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
| 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
 
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
 
i like to use what I call a poor-man's version of list processing...

[C1:C50000] = [A1:A50000+B1:B50000]

HTH
Dana DeLouis
 
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 said:
i like to use what I call a poor-man's version of list processing...

[C1:C50000] = [A1:A50000+B1:B50000]

HTH
Dana DeLouis
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
Click to expand...
Click to expand...
 
Back
Top