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
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
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:RC)" '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
Range("A1:A20").FormulaR1C1 = "=SUM(RC:RC)" 'Writes formula from A1 to A20
6 – Avoid using .select
Every time something like this appears in your code:
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:
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.