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… 🙂
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.
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 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
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.
Click here to register for the webinar (it's free!)
I look forward to seeing you there! 🙂
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 = TrueHi 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,
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
Sub FINDER()
Search = InStr(ActiveCell.NumberFormat, Chr(128))
Columns(“H”).Select
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
err.clear
end if
if lngrw0 then
‘write code here for What you want to do if you found the row with your
‘string
end if
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
COLUMN A B C D E
S.No NAME TAG CAT Qty
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.
COLUMN A B C D E
S.No NAME TAG CAT Qty
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
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,
AppWord.Selection.Paste
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
Sheets(“D3_18”).Select
Columns(“I”).Select
Dim AppWord As Word.Application
Set AppWord = CreateObject(“Word.Application”)
AppWord.Visible = False
Selection.Copy
DocPath = “I:\Mazaklathes\DOOSAN\Active_3100\Staging\D3_18.txt”
‘create and save txt file
AppWord.Documents.Add
‘
‘
‘
‘
‘
AppWord.Selection.Paste
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
wkbCsv.Sheets(1).Cells.Copy
Range(“A2:Q2” & FinalRowCsv).Select
Selection.Copy
Some of these hang on “Selection.value=Selection.value
I thought it would faster than Selection.copy and Selection.PasteSpecial
Range(“BM3”).Select
Selection.Copy Destination:=Range(“BM4”, Range(“BM” & Rows.Count).End(xlUp))
Calculate
Range(“BM4”, Range(“BM” & Rows.Count).End(xlUp)).Select
Selection.Value = Selection.Value
Call ClearZeros.Clear_0_Values
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.
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
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
Worksheets(“Sheet1”).Select
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
Worksheets(“Pairings”).Select
Me.ListBox1.ListIndex = -1
End Sub
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
Next
response = dict.Count
Range(“w4”).value = response
End Sub
Hello Jon, I was excited reading about how to get rid of the “select” where it is possible. I have the following code:
“Sheets(“Paneel-2020″).Select
Range(opbr_dag & begin, opbr_dag & eind).Select
Selection.Copy
Range(opbr_dag & begin_best).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False”
and thought I could make the easier/faster by recoding to:
“Sheets(“Paneel-2020”).Range(opbr_dag & begin, opbr_dag & eind).Copy Sheets(“Paneel-2020”).Range(“opbr_dag & begin_best”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False”.
Did not test the “new” code line, I find it much more difficult to understand what the code is doing and perhaps easier to make typing misstakes.
Would like your comments on this.
Kind regards,
Eddy
I experience worksheet bleeding using the .select code when switching to another sheet. Was hoping your post would deal with that, oh well.
I’m using this code to try to copy 4 rows of data from one Worksheet to the first row of another Worksheet. When I run the code, I get:
Run-time error ‘1004’:
Application-defined or object-defined error
In case it matters, I am running Excel on a Mac
Here’s the code (by the way, the main code below is on 1 line of code not 2 but it is showing as 2 separate lines below):
Sub aaaCopyData()
Sheets(“Sheet1”).Range(“E2:H”).Copy Sheets(“Sheet2”).Range(“A1”)
End Sub
I’m using an excel xlsm doc written by somebody else to parse data. However, I am getting this error:
Run-time error ‘9’:
Subscript out or range
This is the line of code throwing the error:
Sheets(“2 RawData”).Select
I know that it could mean that that sheet doesn’t exist, but it definitely does. I’ve also checked for leading or trailing spaces, but didn’t find any. I’m hesitant to change this code because I don’t know anything about VBA. Also, if I were to copy the data manually to the excel file and run the macro, it works fine. It only has an issue when I try to automate it with python.
I would like your opinion on this issue if possible.