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 maintainable 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 101 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
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 301 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.
Get the spreadsheet used in the tutorial above.
Also get updates when new blog or video tutorials are released.
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 😉
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
You are very welcome kevin.
If you have any questions feel free to ask, gives me ideas for content 😉
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
Hi Eric
Thanks for your kind words.
I keep meaning to build this blog out more.
Oh well good intentions…
😉
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
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 trueMany thanks for your question.
Cheers
Sean
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
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.
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
HI Eli
Thanks for your question, you will find a post that I wrote “just for your” 😉 at
https://businessprogrammer.com/excel-vba-consolidate-data-from-multiple-sheets/
Enjoy…
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
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.
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 ?
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.
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.
HI Frank
Just saw your comments today, I’m afraid life got in the way and did not see the comment notification.
If you still have this problem email me your spreadsheet and I may make a video for the answer.
However in a nutshell, I suspect that I would use vba to extract the rows containing the data I wanted to a different sheet in my workbook, or a different part of the same sheet.
Then I would have the chart ranges point to that same extract area.
Therefore when I do an update the latest data would be sent to the chart input and the chart would update.
You could model this process manually first to get the gist of what I mean.
Hope this helps.
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!
HI Daniel
Sorry for the late reply, just saw your comment today.
Imagine a column of data, or in this a case a column of cells that you want to populate with numbers, i.e. 1,2,3,4 etc.
The first line rngNumbers.Resize(1,1).value = 1 means resize the range “rngNumbers” (Earlier defined to be that column) to a size of 1 row and one column (basically one cell) then put the value 1 into it.
The next line says…
Now resize the rngNumbers range to be 15 rows long (We are assuming that rngNumbers was previously defined to be only 1 column wide).
Now to this resized range issue the Excel DataSeries command with a step value of 1, which will take the value of 1 added in the previous command and increment it by one thus resulting in a vertical column of numbers from 1 through 15.
Hope this helps
Hi Sean, I was reading your interesting post, I’m wondering if I can avoid using Select also to change properties of a picture/shape, can’t find a good code for this:
ActiveSheet.Shapes(ImgName).Select
Selection.ShapeRange.Height = x
Selection.ShapeRange.Width = y
Selection.Characters.Text = z
Selection.ShapeRange.IncrementLeft 1
Selection.ShapeRange.IncrementTop 1
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 255)
Selection.ShapeRange.Line.ForeColor.SchemeColor = 13
etc.
Any solution?
Thanks and Kind Regards
Reg
Hello Reg,
I think you could do something like this:
Dim ImgName as Shape
ImgName = Shapes(“…..”)
With imgName
.Height = x
.Width = y
etc.
End With
It works like a charme, thank you!
I have a form with a header and several rows of data. I am using columns A through AB.
Some columns contains dollar amounts, text or dates. Some data is bold or different color font..
I currently have a macro that finds the last row and starts at the first cell in that row and selects each cell in the row and formats each cell a certain way and then enters data. I am using Activecell.offset and with statements to format.
I know selecting each cell slows down the process..
I am looking for code one the last row is found to move from one cell to the next without selecting it and be able to format it etc.
I have searched the internet looking for this but most examples use a range. My problem is I never know where my last row will be from one time to the next. Which makes this more challenging.
I also know you can not loop through a row like you do a column..
Can you please direct me on how to proceed with this.
Hi Mark
I have just created an answer to your question in the How To find the last row in and excel vba range post.
Hope that helps… 🙂
‘Now this time i am in E10 and E10 is my ActiveCell. Throught the ActiveCell Concept E10 is ActiveCell (A1)
‘In Cell E10/Active Cell(A1)
‘Put it with 20 digit (11111111112222222222) with your keyboard
‘F10/ActiveCell(b1) put it 20 with your keyboard
‘In Cell K10/ActiveCell G1 put the value 10 with your keyboard
Sub Test_02()
If ActiveCell.Range(“b1”) = 20 Then
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Rows(“1:1”).EntireRow.Select
Selection.Copy
ActiveCell.Offset(-1, 0).Rows(“1:1”).EntireRow.Select
ActiveSheet.Paste
ActiveCell.Offset(0, 4).Range(“A1”).Select
ActiveCell.Value = Right(ActiveCell, ActiveCell.Range(“G1”))
ActiveCell.Offset(1, 0).Rows(“1:1”).Select
ActiveCell.Value = Left(ActiveCell, ActiveCell.Range(“G1”))
End If
End Sub
the result is show 1111111111 (10 Digit) Seperate in One Cell and 2222222222 (10 Digit) Seperate in other Cell.
This Selection method is work perfectly when i select Single Cell but when i Select 2 Cell ( E10 and E11), it is not work.
You have any better method/soluction about this.