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.


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()


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.


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.


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! 🙂


Your email address will not be published. Required fields are marked *

  • I would like to reference the dynamic range without selecting it and then loop through every cell in the range and add it to a dictionary. Any ideas? Thanks!

    Sub Unique_Dates()

    Dim undt, cell As Range
    Set undt = ActiveSheet.Range(“U4”, “U4”)
    Dim answer As Variant
    Dim test As Variant
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary
    Dim response As Long

    answer = Range(undt, undt.End(xlDown)).Select

    For Each cell In Application.Selection
    If Not dict.Exists(cell.value) Then
    dict.Add cell.value, 0
    End If
    response = dict.Count
    Range(“w4”).value = response

    End Sub

  • My problem is that Sheet1 is a hidden worksheet and when my code attempts to select it, I get an error. If I make Sheet1 visible, the UF activates just fine.

    Private Sub UserForm_activate()
    g = Worksheets(“Sheet1”).Cells(Rows.Count, “A”).End(xlUp).Row
    With Range(“A1:B” & g)
    Me.ListBox1.ColumnCount = 2
    Me.ListBox1.ColumnWidths = “0,150”
    ht = g * 9.65 + 9.65
    Me.ListBox1.Height = ht
    Me.Height = ht + 55
    If g > 1 Then Me.ListBox1.RowSource = .Address
    End With
    Me.ListBox1.ListIndex = -1
    End Sub

  • Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ActiveSheet.Shapes.Range(Array(“Group Craig”)).Select
    Selection.ShapeRange.Rotation = Range(“V9”) * 221

    ActiveSheet.Shapes.Range(Array(“Group Emily”)).Select
    Selection.ShapeRange.Rotation = Range(“v12”) * 221

    ActiveSheet.Shapes.Range(Array(“Group Farran”)).Select
    Selection.ShapeRange.Rotation = Range(“v15”) * 221

    ActiveSheet.Shapes.Range(Array(“Group Kate”)).Select
    Selection.ShapeRange.Rotation = Range(“v18”) * 221

    ActiveSheet.Shapes.Range(Array(“Group Marie”)).Select
    Selection.ShapeRange.Rotation = Range(“v21”) * 221

    ActiveSheet.Shapes.Range(Array(“Group OFF PREM SAM”)).Select
    Selection.ShapeRange.Rotation = Range(“v24”) * 221

    End Sub

    How do i fix this.
    Continual errors and battle to click any cell

  • Range(“Table15[[#Headers],[Outpatient Encounter Date]]”).Select

    How do I fix this error? Seems since an upgrade I am getting these errors I didn’t get in the past.

  • Some of these hang on “Selection.value=Selection.value
    I thought it would faster than Selection.copy and Selection.PasteSpecial

    Selection.Copy Destination:=Range(“BM4”, Range(“BM” & Rows.Count).End(xlUp))
    Range(“BM4”, Range(“BM” & Rows.Count).End(xlUp)).Select
    Selection.Value = Selection.Value
    Call ClearZeros.Clear_0_Values

  • I’m trying to copy a column from a sheet and make it a text file with the code I have it will work great some times and sometimes it won’t. Sometimes there is nothing in the file and sometimes it throws an error where it says,
    I’m putting my exact code to see if you can help me correct my issues.

    Sub D3_18()

    Dim DocPath As String

    Dim MsgBoxCompleted



    Dim AppWord As Word.Application

    Set AppWord = CreateObject(“Word.Application”)

    AppWord.Visible = False


    DocPath = “I:\Mazaklathes\DOOSAN\Active_3100\Staging\D3_18.txt”

    ‘create and save txt file



    AppWord.ActiveDocument.SaveAs2 Filename:=DocPath, FileFormat:=wdFormatText

    Application.CutCopyMode = False

    AppWord.Quit (wdDoNotSaveChanges)

    Set AppWord = Nothing

    MsgBoxCompleted = MsgBox(“Process complete.”, vbOKOnly, “Process complete”)

    End Sub

  • I am a beginner in macros. I need some support how to create a macro for below requirement. I want to move values from sheet to other when value of cell. Basically trying to consolidate data from sheet 1 to Sheet 2 on a click of a button

    Please see below for sheet 1 data’s ( This cells contains formulas)

    Sheet 1 DATA
    1 A RED M 1
    2 B BLUE F 2
    3 C RED M 0
    4 D YELLOW M 4
    5 E BLUE F 5
    6 F GREEN F 0
    7 G RED M 0
    8 H BLUE M 0
    9 I GREEN F 4

    Sheet 2 data to come out like shown below on click of a button ( Basically I want to move values of Range (B(x):E(x)) if Value of E >0.
    1 A RED M 1
    2 B BLUE F 2
    3 D YELLOW M 4
    4 E BLUE F 5
    5 I GREEN F 4

    Also to note column A gets serial no created and not copied from previous sheet.

    Please help with explanations , considering a beginner

    Thanks in advance for support

  • Sub FINDER()

    Search = InStr(ActiveCell.NumberFormat, Chr(128))
    Selection.Find(What:=Search, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

    End Sub

    Please help me to avoid the .Select in the above code.It makes my code not working properly.How else can i select the column H ?

    Thank you in advance !

    • ‘Here you can Avoid both .select and .activate as I understand the you are ‘looking for the row in column H which carries your search string.
      dim lngrw as long ‘to capture the row number where you search string is found

      on error resume next
      lngrw=sheet1.range(“H:H”).Find(What:=Search, After:=sheet1.range(“H1”), LookIn:=xlValues, _
      LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).row

      ‘Above code would give you the row number in your column H which carries the string..

      if err.number0 then
      end if
      if lngrw0 then
      ‘write code here for What you want to do if you found the row with your
      end if

  • 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,

  • 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

    • 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!

  • 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
    'Paste the Unique values to the temporary sheet in A1
        FilterColumn.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets(RandomName).Range("A1"), Unique:=True
    'Write the values to the UniqueArray
        UniquesArray = Sheets(RandomName).Range("A2", Range("A2").End(xlDown)).Value
    'Deletes the Temp sheet
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...