make a macro run quicker?

zilch4ry

Board Regular
Joined
Feb 27, 2011
Messages
76
hi all

I have recorded a macro, but was wondering if there was anyway of running it without actually running through the macro. What I mean is that as soon as I use the macro you can see it skipping through all of the cells and then end on the last cell I clicked, so what i'm asking is: is it possible to run the macro but without seeing any of this happen?

The macro I recorded is:

Code:
Range("G15:H21").Select
    Range("H21").Activate
    Selection.Copy
    Range("G6:H12").Select
    Range("H12").Activate
    ActiveSheet.Paste
    Range("J15:K21").Select
    Range("K21").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("J6:K12").Select
    Range("K12").Activate
    ActiveSheet.Paste
    Range("M15:N21").Select
    Range("N21").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("M6:N12").Select
    Range("N12").Activate
    ActiveSheet.Paste
    Range("P15:Q21").Select
    Range("Q21").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Range("P6:Q12").Select
    Range("Q12").Activate
    ActiveSheet.Paste
    Range("P15:Q21").Select
    Range("Q21").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("M15:N21").Select
    Range("N21").Activate
    Selection.ClearContents
    Range("J15:K21").Select
    Range("K21").Activate
    Selection.ClearContents
    Range("G15:H21").Select
    Range("H21").Activate
    Selection.ClearContents

The macro involves copying all of the cells and pasting them into a new set of cells on the same worksheet. After the macro has done this is then goes back and deletes all information which was found in the previous cells.

Thanks in advance



Tom
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

woned

New Member
Joined
Feb 15, 2011
Messages
18
The simplest way to fix your problem is by disabling screen updating whilst your macro does its work.

Type this at the beginning of the macro
application.screenupdating = false


Make sure to re-enable it after.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The thing that is slowing the macro down is all of the select, activate, and selection statements. Those need to be cleaned up.

Also, I noticed a pattern in the code, try this:

Edit again:

Code:
Sub foo()
Dim i As Long
Application.ScreenUpdating = False
For i = 7 To 16 Step 3
    With Range(Cells(15, i), Cells(21, i + 2))
        .Copy Destination:=Range(Cells(6, i), Cells(12, i + 2))
        .ClearContents
    End With
Next i
Application.ScreenUpdating = True
End Sub
 
Last edited:

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
The slowest part of your code is the unecessary selecting of cells. Something like this...

Code:
Sub a()

Application.ScreenUpdating = False

Dim i As Long
i = 7
For i = 7 To 16 Step 3


Range(Cells(15, i), Cells(21, i + 1)).Copy Destination:=Range(Cells(6, i), Cells(12, i + 1))
Range(Cells(15, i), Cells(21, i + 1)).ClearContents


Next i

Application.ScreenUpdating = True

End Sub

EDIT: Beaten to it!
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

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