How to improve VBA macros in Excel: Use Range instead of Select

When using macros in VBA for Excel, try to use Range as much as possible. The Range function is faster and less prone to errors. Are you curious to learn more about VBA macros for Excel? Read these articles:

Make a good selection

Using the Select method in VBA is similar to using Activate with one major difference: Select can be used to select multiple objects such as sheets, shapes, and ranges like so, Range(“A1:B10, D1:E10, K6:M32”).Select), while Activate can only be used to activate one single object like Worksheets(“Sheet3”).Activate.

Select is often a better choice when choosing between Select and Activate due to not always knowing which objects in a workbook are selected. However, there are times when even Select is used too frequently or inefficiently such as when using the macro recorder.

Avoid Select to speed up macros

When using the macro recorder, the Select or Activate method causes macros to be slow and prone to runtime errors because it is explicitly referring to one or more specific ranges, sheets, or shapes.

So, while you can start with the recorder, it is good practice to review the code for Select or Activate methods and change them to Range references in order to keep the macro from being too slow and running into runtime errors.

An example of an inefficient code fragment with the Select method could be when the recorder code applies italics to C4:C62. Consider the following:

VBA macro, Select used

The recorder uses the Select method to identify the range. Once you know the right methods and properties --Font.Italic = True-- you can easily rewrite the macro as follows:

VBA macro, Select removed

Macro2() achieves the same results with one line of code and without selecting the range.

So, you can combine the two statements and delete the Select method and the Selection object. This improves the code and makes it less susceptible to runtime errors.

Additional ways to improve macro speed

Removing the Select method from your code can certainly increase the speed of your macros, but that’s not the only way. But keep in mind that you should make the call to apply these options only if your situation demands it:

Turn off automatic spreadsheet calculation (unless absolutely necessary)
Avoid using variants in formulas
Minimize traffic between VBA and the Worksheet
Write blocks of data in a single operation
Avoid evaluating strings

Counterintuitive? Maybe not.

Not using the Select method in combination with the macro recorder may seem counterintuitive but simplifying your code can often make it more efficient. So, next time you decide to run the macro recorder or are in the process of cleaning it up (which you should get in the habit of doing), try removing the selects and making your code more efficient.

With over 80 VBA Challenges available, you can continue learning about Select, Activate, speeding up macros, and much more by signing up at 5miles for a free two-week trial. What are you waiting for?