Can this VBA run faster?

phil133

Board Regular
Joined
May 5, 2015
Messages
248
Office Version
  1. 2016
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,517
Office Version
  1. 365
Platform
  1. Windows
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
 

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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
 

phil133

Board Regular
Joined
May 5, 2015
Messages
248
Office Version
  1. 2016
Platform
  1. Windows
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.
 

phil133

Board Regular
Joined
May 5, 2015
Messages
248
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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!
 

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
642
Move the "ActiveSheet.Calculate" after the "Next I" No need to calculate each action in the loop.

Good luck,

CN.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,140
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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?
 

phil133

Board Regular
Joined
May 5, 2015
Messages
248
Office Version
  1. 2016
Platform
  1. Windows
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?
 

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
642
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
 

phil133

Board Regular
Joined
May 5, 2015
Messages
248
Office Version
  1. 2016
Platform
  1. Windows
Thanks but very small difference. About 10 seconds.
 

Forum statistics

Threads
1,136,444
Messages
5,675,894
Members
419,591
Latest member
mersanko

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
Top