Ringhal
10-08-2015, 01:28 AM
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
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