make a macro run quicker?

[zilch4ry]

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:

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

 

[zilch4ry]

any ideas?

 

[woned]

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]

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:

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

 

[njimack]

The slowest part of your code is the unecessary selecting of cells. Something like this...

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!

 

[zilch4ry]

Thank you all for the help!