Slow macro

T

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".
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

N
  • Question
My code is suddenly really slow
Replies
1
Views
197
RobP
R
L
  • Solved
VBA Error
2 3
Replies
29
Views
593
Joe4
Joe4
M
  • Solved
VBA Excel file attributes
2
Replies
13
Views
456
mrsushi
M
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
223
Tupe77
T
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
366
ItalianPlatinum
I

Forum statistics

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

Share this page

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