Can this VBA run faster?

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
Hi. I have written the following VBA
Code:
Sub calculate_macro1()'
' test Macro
'


'
    Sheets("P_L 2,ATM").Select
    Range("G6:G400").Select
    Range("G6:G400").Value = 100
    For i = 6 To 500 Step 2
    Range("X" & i).Copy
    Range("G" & i).PasteSpecial (xlPasteValues)
    Range("G" & i).Copy
    Range("G" & i + 1).PasteSpecial (xlPasteValues)
    Range("X" & i).Activate
    ActiveSheet.Calculate
    Next i
End Sub

How can I change it so that it runs faster?

Thanks for any help!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
What is it trying to do because, as written, the G6:G400 = 100 can never happen because it is always overwritten by the code G & i pastespecial and G & i + 1 pastespecial
 
Upvote 0
Fairly instant:

Sub calculate_macro1() '
Sheets("P_L 2,ATM").Select
Range("G6:G400").Value = 100
For i = 6 To 500 Step 2
Range("G" & i).Resize(2).Value = Range("X" & i).Value
Next i
ActiveSheet.Calculate
End Sub
 
Upvote 0
Thank you BobUmlas! This is much faster!

Hi steve the fish! I want the starting value of G to be 100 (which impacts X) and then copy X to G as you see.
 
Upvote 0
I have another very similar VBA that needs to be faster. If anyone can help I would be grateful!
This is it
Code:
Sub calculate_macro2()
'
' test Macro
'


'
    Sheets("P_L 2,ITM").Select
    Range("G2").Select
    Selection.Copy
    Range("G4:G5000").PasteSpecial xlPasteValues
    For I = 6 To 4000 Step 2
    Range("AA" & I).Copy
    Range("G" & I).PasteSpecial (xlPasteValues)
    Range("G" & I).Copy
    Range("G" & I + 1).PasteSpecial (xlPasteValues)
    Range("AA" & I).Activate
    ActiveSheet.Calculate
    Next I
End Sub

Thanks!
 
Upvote 0
Move the "ActiveSheet.Calculate" after the "Next I" No need to calculate each action in the loop.

Good luck,

CN.
 
Upvote 0
I have another very similar VBA that needs to be faster

As you state it is very similar can you not apply Bob's logic in post #3 to your new code? what parts do you not understand?
 
Upvote 0
I changed it to this
Code:
Sub calculate_macro1()
    Sheets("P_L 2,ATM").Select
    Range("G2").Select
    Selection.Copy
    Range("G4:G5000").PasteSpecial xlPasteValues
    For I = 6 To 4000 Step 2
    Range("G" & I).Resize(2).Value = Range("AA" & I).Value
    Next I
    ActiveSheet.Calculate
End Sub
Is it normal that it takes 5 minutes 10 seconds?
 
Upvote 0
A small performance gain can be achieved by turning off screen updating ...
Code:
Sub calculate_macro1()
    Application.ScreenUpdating = False
    Sheets("P_L 2,ATM").Select
    Range("G2").Select
    Selection.Copy
    Range("G4:G5000").PasteSpecial xlPasteValues
    For I = 6 To 4000 Step 2
    Range("G" & I).Resize(2).Value = Range("AA" & I).Value
    Next I
    ActiveSheet.Calculate
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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