How to Avoid the Select Method in VBA & Why - Excel Campus
4

How to Avoid the Select Method in VBA & Why

The first TV I remember us having as a kid did NOT have a remote control.  How inconvenient, right?

Every time someone in the family wanted to change the channel or volume, we had to get up off the couch and walk over to the TV to press a button.  You didn’t want to be the one sitting closest to the TV unless you needed some exercise… 🙂

The Select Method is Like Using a TV without a Remote Control

Imagine how many times you pressed the buttons on your TV remote last night.  Probably a lot!  Especially if you are married or have kids.  Now imagine how inefficient it would be to have to walk over to the TV to make every one of those button presses.  What a waste of time!

The Select Method is Slow, Error Prone, and Overused

The Select method in VBA can be just as time consuming for Excel, as it is for you to get up off the couch to change the channel.

The Select method is used to select objects in Excel like worksheets, charts, shapes, and ranges.  Here are a few examples.

Worksheets("Sheet2").Select
Range("B5").Select

These lines of code are usually followed by another common command to copy/paste values, format a cell, insert formulas, etc.

The problem with the Select method is that it can really slow down your macro.  When we use the Select method, VBA has to force the Excel application to update the screen with the selection change (display a new worksheet, scroll to a range/cell, etc.).  This screen update takes extra time and is usually unnecessary.

The good news is that 99% of the time, the Select method can be avoided all together.

Don’t Worry, It’s Not Your Fault!

We tend to see the Select method a lot in VBA code, and this is mostly the fault of the macro recorder.  Don’t get me wrong, the macro recorder is a great tool, but the code it produces can pretty inefficient.

Most of the tasks we automate with VBA do NOT require us to select a workbook, worksheet, or range first.  Let’s look at a simple example of copying and pasting a range of cells from one worksheet to another.

The macro recorder produces the following code when I record my actions to copy range B2:B8 on Sheet1, and paste it on Sheet2.

Sub Macro_Recorder_Copy_Paste()

    Sheets("Sheet1").Select
    Range("B2:B8").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveSheet.Paste

End Sub

The Select method is used 4 times in the code above.  The Selection property is also used once, so that’s really 5 references to a selection for a simple copy paste.

Kick Select to the Curb with Andy

This macro can be made a lot more efficient!

We Don’t Need to Select Ranges Before Most Actions (Methods)

We don’t need to select any sheets or ranges for this simple copy and paste action.  The following macro accomplishes the same task without the Select method, and only requires one line of code.

Sub Simple_Copy_Paste()

    Sheets("Sheet1").Range("B2:B8").Copy Sheets("Sheet2").Range("B2")

End Sub

Checkout my article and video series on 3 ways to copy & paste with VBA for more code examples and explanations.

What’s the difference between the macros?

Let’s say you want to change the TV channel from 2 to 7. The first example from the macro recorder requires you to press the channel + button five times.  The TV has to do extra work to display each channel on the screen as you repeatedly press the button.

The Channel Up Down Button on the TV Remote is Similar to using the VBA Select Method

The simplified macro requires you to press the 7 button on the remote control once, to jump directly to channel 7.  Much faster and easier! 🙂

The Select Method Causes More Errors

I have had quite a few inquiries asking why the following line of code returns an error.

Worksheets("Sheet2").Range("A2").Select

Returns the following error:

Run-time error '1004'
Select method of the Range class failed

VBA Error Select Method of Range Class Failed

This is just a rule in VBA that does not allow us to select a sheet and a range in the same line of code.  If you really want to select the sheet and the range, then we need to break it up into two lines of code.

Worksheets("Sheet2").Select
Range("A2").Select

The Select method can be prone to lots of other errors as well.  It is important to properly qualify the code by first selecting the parent objects in the hierarchy like workbooks and worksheets, before selecting a range.

Therefore, you should only select cells when you absolutely have to.  This is usually when you want to direct the user to a new location in the workbook after a macro has run.

How Else Can We Avoid the Select Method?

We want our macros to be as efficient as possible so we can save time with our jobs, and score extra bonus points on the performance reviews!  And ditching the Select method is one simple way to really improve our macros.

The macro recorder is an awesome tool for getting snippets of code, and learning how to reference the Objects, Properties, & Methods in Excel. However, it produces the code for the actions we have to take in Excel’s user interface. Those actions are not always necessary when programming the computer to do the same task with a macro.

If you think your macro has some unnecessary Select methods in it, but you’re not sure how to get rid of them, then leave a comment below with your code.  It will be great to see some other examples of how we can make our code more efficient.  And I know the Excel Campus community has some smart VBA coders that will be happy to help make some suggestions. 🙂

Free Training on Macros & VBA

I will be holding a free webinar on “The 7 Steps to Getting Started with Macros & VBA”.

If you are wondering why you should learn macros & VBA, or are frustrated with the progress you are currently making, then this training is for you.

We are going to write our first macro, and I will explain how it all works in simple terms.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to register for the webinar (it’s free!)

I look forward to seeing you there! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 4 comments
Scott - September 2, 2017

Hi Jon,
I have a project that I’m working on that I’d like some help from an expert on.

I need to know how to write a copy and paste macro that I can use at any range I choose. I don’t know much about writing VBA so I record my macros at this point. I’ve recorded a macro to copy and paste several times to create a grid of information. The initial macro recording is done in a certain range. So now I want to run the same macro to do the same task over and over as I move down the same worksheet. Of course the macro was recorded to run in a specific range. How would I identify where to begin the macro so that it will perform the same task in the range I’m in?
I hope that I’ve explained clearly what it is that I’m trying to do. I honestly don’t where to turn to. I’ve watched several videos all day and I can’t find specifically what I need to know.

Thank you,
Scott

Reply
Daniel Lamarche - January 19, 2017

Hi Jon. Nice video and good technique to follow. I’m an experienced Excel VBA instructor and early in the course I encourage the particiants to work “remotely” as the code runs much faster. How to do a medium size macro while the active cell stays in A1.
After a course I had a guy once that spend a couple of hours removing most .Select and shaved 60 percent of the time it took to run the macro. Of course freezing the screen helped too!!!

I will share your Web site to my beginner participants in the future.

Thanks again

Reply
    Jon Acampora - January 20, 2017

    Thank you Daniel! I really appreciate your support. In my VBA Pro Course I also teach how to use arrays to improve performance. Loops can can cause to slow down significantly if the code within the loop is having to go through the VBA > Excel bottle neck at each iteration. Loading the data to arrays and looping through the array can significantly improve performance. Thanks again!

    Reply
Chris - June 2, 2016

Hi Jon –

Great post, and even better analogy! I’ve gotten the hang of removing .select (or .activate) from my code, but here is an instance where I haven’t found a way around it. I don’t know how to embed the code nicely in this comment, so apologies for the lack of aesthetics.

High level goal of the macro: I give the user an input box to choose a specific column in a table of data (customer #, for example). The macro then creates new files all saved in a user defined folder. Each of these files has the data stripped down to show data for only one customer # (i.e. if there were 3 unique customer #s, there will be 3 separate files created).

I accomplish this with the following method:
FilterColumn.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets(RandomName).Range(“A1”), Unique:=True

This gets me the unique values from the user selected column. The goal is to write these values to an array that I then loop over when I create the individual files. However, I can’t find a way to write the unique values directly to an array. So I create a temporary sheet (with a very random name to minimize the probability of collision) and write the values there. Then I write the values to the array, and finally delete the temp sheet.

When I create the new sheet, Excel automatically activates that sheet. So I have to activate the original sheet to do the first copy. Then I have to activate the temp sheet to do the second write (to the array).

Thoughts on this?

~ Chris

Code follows:

'Land unique values into a temp sheet, and write them to memory in UniquesArray
'Define range of only the column that the user selected
    Set FilterColumn = Sheets(OriginalSheetName).Range(Cells(1, UserResult.Column), Cells(LastRow, UserResult.Column))
    
'Create a temporary sheet with the RandomName
    Sheets.Add.Name = RandomName
    Sheets(OriginalSheetName).Activate

'Paste the Unique values to the temporary sheet in A1
    FilterColumn.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets(RandomName).Range("A1"), Unique:=True
    Sheets(RandomName).Activate
'Write the values to the UniqueArray
    UniquesArray = Sheets(RandomName).Range("A2", Range("A2").End(xlDown)).Value

'Deletes the Temp sheet
    Application.DisplayAlerts = False
    Sheets(RandomName).Delete
    Application.DisplayAlerts = True

Reply

Leave a Reply: