Power Excel vba secret, avoid using select

I see it all the time, code that selects one thing, then another, then selects something else in order to navigate and write data in an Excel spreadsheet.

Instead understand that the Microsoft Excel object model and your vba code will be more professional, robust and mantainable if you don’t select anything but instead interact with the Excel objects directly.

Check out the video below and read this short article, both tackle the same problem but with different code and projects, it’s well worth you time.

Basically you will be able to do more cool stuff because you are now programming the application rather than just emulating user keystrokes.

Example 1
01  Sub NotGood()
02    Dim i As Integer
03    ActiveWorkbook.Worksheets(2).Select
04    Range("A5").Select
05    Selection.Value = "Enter Numbers"
06    For i = 1 To 15
07       ActiveCell.Cells(2).Select
08       Selection.Value = i
09    Next
10  End Sub 
Example 2
01   ' Least amount of code but no variables
02   '(variables are better as they give you more flexibility in larger programs)
03    Sub MinimumAmountOfCode()
04      With ActiveWorkbook.Worksheets(2).Range("C5")
05        .Value = "Enter Numbers"
06        .Offset(1).Value = "1"
07        .Offset(1).Resize(15).DataSeries Step:=1
08      End With
09    End Sub

The "With" statement on lines 4 and 8 above are a useful Object Oriented construct, it means the changes you make on lines 5, 6 and 7 (because they start with the dot operator) are applied to the object referenced on line 4.

This saves you typing and saves the processor having to navigate the object tree each time, very relevent in large loops.

Example 3
01  Sub Better()
02    Dim wbk As Workbook
03    Dim rngCell As Range, rngNumbers As Range
04    Dim i As Integer
05    
06    ' Set up two references
07    Set wbk = ActiveWorkbook
08    Set rngCell = wbk.Worksheets(2).Range("E5")
09 
10    rngCell.Value = "Enter Numbers"
11   
12    ' Populate 1 to 15
13    For i = 1 To 15
14      rngCell.Offset(i).Value = i
15    Next
16    ' Done
17 
18    '=========================================================
19    ' Following is for extra credit !  :-)
20    '=========================================================
21    
22    ' Get reference to numbers range
23    Set rngNumbers = rngCell.CurrentRegion
24    Set rngNumbers = rngNumbers.Resize(rngNumbers.Rows.Count - 1).Offset(1)
25   
26    MsgBox "Numbers entered click OK to try a different way", vbExclamation
27    rngNumbers.Clear
28 
29    MsgBox "Range Cleared, now lets populate it a different way", vbExclamation
30    ' Enter numbers without needing to loop  
31    rngNumbers.Resize(1, 1).Value = 1
32    rngNumbers.Resize(15).DataSeries Step:=1
34
35    ' Now put those numbers somewhere else
36    rngNumbers.Offset(, 4).Value = rngNumbers.Value
37  End Sub

Which of the above examples are best? Well it depends…

Example 1 is the worst as it is just a “macro” (copying keystrokes) and thus has the least options for building a strong application with options for future extendibility.

We need to choose then between Example 2 and Example 3.

Sign up below and get notified when new Business Programmer blog or video tutorials are created.

Example 2 does not use any variables, this is good if you need code to run really really fast (unless it is in a huge loop you ain’t gonna notice the difference.) but not so good from a readability point of view (especially when you get into hundreds of lines of code.)

Example 3 uses variables “Dim wbk As Workbook” etc so instead of writing..

wbk.Worksheets(2).Range("E5").value="Enter Numbers"

You can instead just use

10    rngCell.Value = "Enter Numbers"

(very useful if you are writing to that cell in different parts of the code) this is also faster for the processor as it does not have to navigate across each dot.

Example 3 gets my vote, because as your program does more things (and therefore get more complex) this coding style will be the easiest to maintain and help you to continue thinking like a human rather than a machine spewing 001001001110101101111001 😉

Free Email Updates Get the latest content first.

If you are new to VBA coding, Start with this book.


Disclosure:- If you buy this or any book using the link above (at the moment amazon.com only). Amazon will give me a small commission.

If you have some coding experience, then this or both books may be best.


Disclosure:- If you buy this or any book using the link above (at the moment amazon.com only). Amazon will give me a small commission.

Thanks for reading this post

If you have any queries or comments please leave them below as they help to improve the quality of the information that I provide.

If you found this article useful then don't forget to share it by clicking on one of the social media buttons below.

18 Comments

  1. Kevin

    October 8, 2012 at 1:49 am

    good post. Answered my question. I still am not that good at excel for using variables all the time. But I will endeavour to get there!!! :) tks

    • Sean Johnson

      October 8, 2012 at 8:29 am

      You are very welcome kevin.

      If you have any questions feel free to ask, gives me ideas for content 😉

  2. Eric Light

    May 9, 2013 at 9:36 pm

    Hi Sean,

    Well said – I was searching for an example of why VBA dev’s should avoid using the .Select statement (to save me from having to type up something myself), and your post came up.

    Couldn’t have said it better myself. Thanks!
    Eric

    • Sean Johnson

      May 13, 2013 at 2:42 pm

      Hi Eric

      Thanks for your kind words.
      I keep meaning to build this blog out more.

      Oh well good intentions…

      😉

  3. Pingback: Excel macro – Avoiding using Select | Ask Programming & Technology

  4. Ashish Rathi

    February 24, 2014 at 4:58 am

    Hello Sean,

    How do you avoid using select in an operation like copy pasting a range of cells from one workbook to another.
    For example, I have such a piece of code in my macro –

    inputfilename.Activate
    Sheets(“Sheets1″).Select
    Range(Cells(rownumber, 3), Cells(rownumber, 38)).Select
    Selection.Copy
    outputfilename.Activate
    Sheets(Sheets1”).Select
    Range(Cells(count2 + 1, 4), Cells(count2 + 1, 39)).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Here,
    inputfilename is the input excel file from which I am copying cells (these cells have formulae in them).
    outputfilename is the output excel file where data is pasted as values
    rownumber and count2 are variables of a ‘do while’ loop

    How can one avoid a select command here?

    Regards,
    Ashish

    • Sean Johnson

      February 24, 2014 at 10:23 am

      Hi Ashish
      This is your answer I believe…

      Sub CopyPasteCells()
       dim wbkSource as workbook, wbkTarget as workbook
       dim rngSource as range, rngTarget as range
      
      do
        ' other do while code here ...
      
        set wbkSource = workbooks.open("path to workbook source")
        'or if already open then...
        set wbkSource = workbooks("Name of workbook")
        
        set rngSource = wbkSource.Worksheets("Sheet1").range(cells(rownumber,3), cells(rownumber, 38))
      
        set wbkTarget = workbooks.open("path to workbook target")
        'or if already open then...
        set wbkTarget = workbooks("Name of workbook")
      
        set rngTarget = wbkTarget.worksheets("sheet1").range(Cells(count2 + 1, 4), Cells(count2 + 1, 39))
      
        rngSource.copy
      
        rngTarget.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      
        ' or if you do  not need the formatting this is better.
      
        rngTarget.value = rngSource.value ' no copy paste clipboard delegation needed here.
      
        ' other do while code here....
      
      loop while ' some condition is true 
      
      

      Many thanks for your question.

      Cheers

      Sean

  5. IMRAN

    January 5, 2016 at 10:10 am

    Hello Sean,
    I have large amount of data in Excel work book. I want to auto select data from drop down list. While data saved in some other sheet. Please Suggest me Code and method to using it.
    Thanks,
    Imran

    • Sean Johnson

      January 8, 2016 at 6:11 pm

      Hi Imran

      Thanks for the comment, you just gave me a great idea for an article.

      I have recorded a video and uploaded it to youtube to handle this specific question for you.

      I have also embedded it in a relevant post, you can find it here.
      Excel range from dropdown list

      Hope that helps.

  6. Eli

    February 7, 2016 at 6:06 am

    Hey Sean,
    I have a macro that finds various text strings in on a sheet( the data is brought in from the web using web query, and needs to be sorted and rearranged completely) and the selects there range, copy pastes it in a desired location. I am just learning vba, and would like to improve my macro. After I saw these brilliant vid, I decided to try Using this more efficient method of copy-pasting data without select. My only impediment now, is: once vba find the text, it needs to copy all contiguous rows until first empty row(NOT end of column), the number of rows changes each time. With the select method I use: range(selection,selection.end(xldown)). Select. How would this be done without select?
    Any direction is appreciated! Your articles have helped a lot.
    Thank you,
    Eli.
    I can share workbook/ macro that I have if it would help

  7. Nick

    March 8, 2016 at 1:57 pm

    Hello Sean,

    Thank you for the detailed post. Do you know of a way to focus the user’s cursor on a specific cell without using Select? For example, I currently have the last line of a function as: Range(“A1”).Select

    Thank you,
    Nick

    • Sean Johnson

      March 8, 2016 at 2:19 pm

      Hi Nick

      The whole purpose of not using select is to not have your programming driven by the need to select.
      If you actually want to leave the cursor in a particular cell when you have finished processing, then it is
      OK to select that cell.

      • Nick

        March 8, 2016 at 2:36 pm

        Thanks for the prompt reply.

        Do you think it is more efficient to use Application.Goto Range(Range(“A1:A1”).Address), False instead of Range(“A1”).Select ?

        • Sean Johnson

          March 8, 2016 at 4:04 pm

          I don’t think efficient comes into it if you are using the select as the final resting place for your cursor, however application.goto Range(“someRange”), True is very useful, as the “True” parameter sets the selected cell to the top left of the spreadsheet window.
          This is useful if you want to control the apperance of the worksheet.

  8. Elizabeth Lopez

    March 30, 2016 at 3:37 am

    Hi, im trying to copy and paste a range of cells from one sheet to another. But im getting an error that expression not define. can you please check? what is the best alternative to this?
    Here is my code:

    Worksheets(“Other Benefits RI”).Range(“F10:I19”).Select
    Selection.Copy
    Sheets(“Summary”).Visible = True
    Sheets(“Summary”).Select
    ActiveSheet.Range(“L21”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(“Summary”).Range(“F10:H13”).ClearContents
    Worksheets(“Other Benefits RI”).Visible = False

    Worksheets(“Other Benefits”).Range(“F10:I19”).Select
    Selection.Copy
    Sheets(“Summary”).Visible = True
    Sheets(“Summary”).Select
    ActiveSheet.Range(“L24”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(“Summary”).Range(“F10:H13”).ClearContents
    Worksheets(“Other Benefits”).Visible = True

    Worksheets(“Other Benefits IC”).Range(“F10:I19”).Select
    Selection.Copy
    Sheets(“Summary”).Visible = True
    Sheets(“Summary”).Select
    ActiveSheet.Range(“L15”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(“Summary”).Range(“F10:H13”).ClearContents
    Worksheets(“Other Benefits IC”).Visible = False

    Worksheets(“Other Benefits EC”).Range(“F10:I19”).Select
    Selection.Copy
    Sheets(“Summary”).Visible = True
    Sheets(“Summary”).Select
    ActiveSheet.Range(“L18:O20”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(“Summary”).Range(“F10:H13”).ClearContents
    Worksheets(“Other Benefits EC”).Visible = False

    Worksheets(“Manhour Cost Savings”).Range(“I21”).Select
    Selection.Copy
    Sheets(“Summary”).Visible = True
    Sheets(“Summary”).Select
    ActiveSheet.Range(“I3”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(“Manhour Cost Savings”).Visible = False

  9. Frank Cote

    July 12, 2016 at 10:02 pm

    I have a problem with response time charts that I need to build. Every day I get two CSL files of transaction response times. I import these into a spreadsheet using the wizard. Fields are separated by bars (|).
    I make a chart showing response time throughout the day. There are three columns I’m concerned with, a date-time column, B, two response time columns. So, I can do it with a macro but only on the same sheet over and over because the next sheet has more or less rows depending on how many transactions occurred; I never know how many.
    So how do I select only the rows with data and suck that into my chart?
    I’m really new to VB with Excel so don’t be afraid to dumb it down.
    Love your videos, your the only one I’ve found that is doing this complex stuff.

  10. Daniel Hayward

    August 12, 2016 at 9:54 pm

    Can you explain to me what is going on with these two lines

    I understand the top one, but it seems like because they are coupled together.
    rngNumbers.Resize(1, 1).Value = 1
    rngNumbers.Resize(15).DataSeries Step:=1

    Thanks!

Leave a Reply

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