Slow macro

[topi1]

I use the following vba along with several other vbas (in sequence). Why does it often really get very slow?

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

 

[jolivanes]

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

Cells(i, 26).Copy
Cells(Rows.Count, 26).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

use this

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.

 

[Alex Blakenburg]

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.

 

[topi1]

Thank you both. I added 1 sec pause before calling this vba. That seems to have worked. Will see if it remains so. TY.

 

[Artik]

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.

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

 

[topi1]

@Artik Thank you. I added that. It made it faster. TY.