Slow macro

topi1

Board Regular
Joined
Aug 6, 2014
Messages
190
Office Version
  1. 2010
I use the following vba along with several other vbas (in sequence). Why does it often really get very slow?

Rich (BB code):
Sub Copy_Five_And_Add_Nine()
    Dim lastRow As Long
    Dim i As Long
    

    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
 
    For i = 1 To lastRow
        If Cells(i, 2).value = 5 Then
         
            Cells(i, 26).Copy
         
            Cells(Rows.Count, 26).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            
          
            Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).value = 9
        End If
    Next i
    
    Application.CutCopyMode = False
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If Column B has 10 rows of data I can't see why it should be slow.
Besides that, what you use in the one line (5th line from the bottom), you could use the same on the other line(s)
Instead of this
Code:
Cells(i, 26).Copy
Cells(Rows.Count, 26).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
use this
Code:
Cells(Rows.Count, 26).End(xlUp).Offset(1, 0).Value = Cells(i, 26).Value
This last suggestion has nothing to do with slowing down your code.

It would be nice to know why this part of the sequence was chosen as the one that slows down your computing.
 
Upvote 0
In addition to Jolivanes comments add Application.ScreenUpdating = False at the beggining of your code and Application.ScreenUpdating = True at the end of your code.
 
Upvote 0
Thank you both. I added 1 sec pause before calling this vba. That seems to have worked. Will see if it remains so. TY.
 
Upvote 0
Formulas and/or events triggered during the run could also be the cause of the macro's slow performance. You would need to disable calculations and events while the macro is running.
VBA Code:
Sub MyProc()
    dim ...
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    ...
    do something
    ...
   
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
End Sub

Artik
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top