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

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

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

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

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

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