7 Ways to speed up your macros Cover

Share with the world

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on reddit

Make a donation

You may also like...

7 Ways to speed up your macros

This is for you who are already used to Macros in your spreadsheets but realize that they could run a little faster.

I’ll show you a series of codes and very simple tips that can help you save even more time with your automated spreadsheets. Let’s get to them.

And keep in mind that if these tips are not enough, or you just want to learn some others, you can check our other post on the topic entitled 9 Ways to speed up your macros, the tips you see here and there are totally different, so make sure you check both of them for better results.

If you happen to be a Portuguese speaker, you can check this very same post in your language, right here.

1 – Disable screen updating

This code prevents Excel from updating the screen showing all the movement of writing formulas, moving in spreadsheets, or anything that your macro does. This means that the computer does not have to spend time or resources updating the image to be displayed on the screen.

Application.ScreenUpdating = False 'Desable screen updating 

'Your code goes here

Application.ScreenUpdating = True 'Enable screen updating

2 – Manual calculation mode

By default, Excel uses the automatic calculation mode, which means that whenever you change the content of any cell it recalculates all the others in all the sheets of your file, and the more formulas there are, the longer it will take.

One way to speed up your Macro is to disable the automatic calculation mode, and only enable it when it is necessary to recalculate something in the spreadsheet.

Application.Calculation = xlManual 'Makes calculation manual, not automatic

'Your code goes here

Application.Calculation = xlAutomatic 'Makes calculation automatic again

3 – Refresh only the current spreadsheet (tab)

This can be combined with the previous one, and it only makes sense if your workbook has more than one spreadsheet (tab).

ActiveSheet.Calculate 'Calculates formulas in the active sheet only

What it does is update only the spreadsheet that is active, while in automatic calculation mode it updates all formulas on all spreadsheets, with this code you will only update the one that is currently active.

4 – Avoid using the clipboard

When we copy and paste, we force the computer to store the data we copy in its memory. To speed up, and not use the computer’s memory, you can use the codes below.

Instead of using something like:

Range("A1:A200").Copy
Range("B1").PasteSpecial
Application.CutCopyMode = False 'Cleans the clipboard

Try something like:

Range("A1:A200").Copy Destination:= Range("B1")

Both codes above copy range A1:A200 to cell B1, but the second one does it without using the clipboard, thus, faster.

Copying and pasting values only

Instead of this:

Range("A1:A200").Copy
Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode = False 'Cleans the clipboard

Use this:

Range("B1:B200").Value = Range("A1:A200").Value

Copying and pasting formulas only

Instead of using:

Range("A1:A200").Copy
Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False 'Cleans the clipboard

Use:

Range("B1:B200").Formula = Range("A1:A200").Formula

5 – Inserting formulas in multiple cells at the same time

It is likely that you have cells with the same formula, usually in cases where you write the formula and then drag or copy it to the other cells that will be the same.

Using the previous code already speeds up, copying, but there is a faster way.

Instead of using something like:

Range("A1").FormulaR1C1 = "=SUM(RC[1]:RC[5])" 'Writes formula in cell A1
Range("A1").Copy
Range("A1:A20").PasteSpecial xlPasteFormulas 'Copies the formula from A1 until A20
Application.CutCopyMode = False 'Cleas the clipboard

Try using:

Range("A1:A20").FormulaR1C1 = "=SUM(RC[1]:RC[5])" 'Writes formula from A1 to A20

6 – Avoid using .select

Every time something like this appears in your code:

Range("A1").Select

Excel will select that cell in the same way that you would do with your mouse, which is unnecessary because it takes processing time to do this, and you can write almost everything in your macro without using .select.

If you need to select a cell, object, spreadsheet, workbook, do this instead:

Range("A1").Activate

7 – Keep your code clean

If you build most parts of your macros using the macro recorder, a good practice is to open and analyze the newly written code.

The recorder writes everything you do, including those unnecessary parts, such as moving the scroll bar, selecting any cell by accident, or anything else you do while recording the macro that has nothing to do with what you actually want to do.

When analyzing the newly created code you will also notice that you can improve your code using the other tips described here, replacing some parts and deleting others.

Now make sure you check our other 9 ways to speed up your macro, and in case you want to make your codes more clear and easier to read, you can check how here.

Function Excel is 100% free, consider donating and sharing

Share with the world

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on reddit

Make a donation

Comment or ask

Check these other posts we have

SPLIT Function Cover

SPLIT function

Currently, on the day I write this post, Microsoft Excel does not contain a SPLIT function that can well… split the content of a cell

Read More »