Efficent arithmetic loop

[Ringhal]

Hi All

I have this loop that adds two cells together and puts the answer in a third cell. The problem is that it runs over at least 15000 cells and takes a fairly long time (this code runs for about 30 seconds). The procedure I have runs multiple loops on various sheets and ranges and takes about 5 minutes to run. I am hoping the VBA experts here know a way to speed up this code.



Sub Test()
Dim i As Long, LastRow2 As Long

With Application
.ScreenUpdating = False
.Cursor = xlWait
End With


With Sheet2
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 2 To LastRow2
Application.StatusBar = i & " / " & LastRow2
.Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2)
Next i

End With

With Application
.ScreenUpdating = True
.Cursor = xlDefault
.StatusBar = False
End With
End Sub


I did try this but got a Type mismatch error (for obvious reasons):


Sub Test2()
Dim i As Long, LastRow2 As Long

With Application
.ScreenUpdating = False
.Cursor = xlWait
End With


With Sheet2
LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C2:C" & LastRow2).Value = .Range("A2:A" & LastRow2).Value + .Range("B2:B" & LastRow2).Value
End With


With Application
.ScreenUpdating = True
.Cursor = xlDefault
End With

End Sub

[snb]

Sub M_snb()
[c1:C15000]=[if(A1:A15000="","",A1:A15000+B1:B15000)]
End Sub

or


Sub M_snb()
sn=cells(1).currentregion.resize(,3)

for j=1 to ubound(sn)
sn(j,3)=sn(j,1)+sn(j,2)
next

cells(1).currentregion.resize(,3)=sn
end sub

[Ringhal]

Thanks snb for the quick reply.
I had always used a loop and this is the first time I had to do it over a large number of rows. This is extremely useful know. The finer details I'll work out for myself :)

[snb]

The essence is that you write to the worksheet only once.

[Ringhal]

Is there anyway to use a variable or string for this as I use each range multiple times?