To Optimize VBA Code for FASTER Macros_3

9. Reduce the number of lines using colon(:)

Avoid multiple statements especially when they can be clubbed into one line.

For example - See these 2 macros

SLOW MACRO:-

With Selection
.WrapText = True
.ShrinkToFit = False
End With

FAST MACRO:-

With Selection
.WrapText = True: .ShrinkToFit = False
End With

As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.

10. Prefer constants

But many of us don't follow it. Like
Dim Pi As Double
Pi = 3.14159
instead use
Const Pi As Double
Pi = 3.14159
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

11. Avoid Unnecessary Copy and Paste

Instead of:-

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False
'Clear Clipboard

Use this:-

'Bypass the Clipboard if only formulas are required
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula
'Same can be done with FormulaR1C1 and Array Formulas.


12. Clear the Clipboard after Paste

Instead of:-

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False
'Clear Clipboard

Use this:-

'Bypass the Clipboard
Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")


13. Avoid 'Macro Recorder' style code.

The code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
[A1].Interior.Color = vbRed
rather than
Range("A1").Select
Selection.Interior.Color = vbRed
Using too many Select and Selection effects the performance drastically.