When you work in Excel, you select cells and ranges a lot. Often, you can automate some of your work using simple macros. The macro recorder can get you started, but the recorder uses explicit references that can be difficult to maintain and enhance. In this article, we’ll review selection tasks using the Select method (the recorder’s preference). Then, we’ll look at how to reference the Range object to accomplish the same thing more efficiently.
There’s nothing wrong with using Select; it’s direct and easy. However, as your code and your skills grow, you’ll find that Select-less code runs faster and is easier to update.
I’m working in Excel 2016 on a Windows 10 64-bit system. The downloadable .xlsm and .xls files contain several simple macros using the statements discussed in this article.
LEARN MORE: Office 365 Consumer pricing and features
About the Range object
Excel’s Range object represents one or more cells and provides easy programmatic access to the cells and ranges you need for common tasks such as:
- Inserting values
- Referencing a cell or range
- Selecting a cell or range
- Copying or moving the contents of a cell or range
- Formatting a cell or range
- Looping or searching within a range
SEE: Crash Course: Microsoft Excel (Tech Pro Research)
Perhaps the easiest (but not necessarily the most efficient) way to select a cell or range uses the Range object’s Select method in the following form:
The reference argument is extremely flexible because it accommodates a single cell, noncontiguous cells, a range, and noncontiguous ranges.
Table A shows how versatile the Range object’s reference argument is.
The pattern is easy to see: All the references are wrapped in a single set of quotes and each reference is separated from the others with a comma character.
The Select method actively moves the selection to the referenced cell or range. Once there, you can do a lot. For instance, you might want to enter data, add or change or format, or even loop through the cells in the range. Let’s look at a couple of simple examples:
'Select a specific cell and enter value.
ActiveCell.Value = "This is A1.
'Select noncontiguous cells and set font color to red.
Range("A1, C2, D1, E4").Select
Selection.Font.Color = vbRed
The first procedure explicitly selects cell A1 before entering the string “This is A1.” The second procedure explicitly selects four noncontiguous cells before setting the font color. Both change the current selection before taking action on that selection. (It’s important to note that this syntax assumes the active sheet, which is implicit to the Range object.)
As you can see, explicit selecting is certainly easy. Explicit selecting is also, almost always, unnecessary. Learning to rewrite your code to use implicit selection is one of the easiest ways to write more efficient code. It isn’t necessary, but making the switch will bump up your skillset a notch or two.
Do a bit of research and you’ll find that serious developers object to Select because of its inherently direct selection. Despite Select’s ease of use and flexibility, the result is a macro that repeats the same exact action. That’s fine if that’s all you need–and I don’t advocate working harder than necessary. However, there’s no arguing that working with objects is superior because you can take action without changing the current selection. For instance, you can replace these two statements:
Cell.Value = "This is A1.
Range("A1").Value = "This is A1"
Excel enters the string “This is A1” in cell A1 without changing the current selection, which is the better choice. Admittedly, reducing two statements to one doesn’t seem like a big deal. But once you apply this guideline to all of your code, you’ll begin to see why it matters.
Let’s look at a simple looping task using both explicit and implicit selection. Both use a For loop to enter the values 1 through 5 in B2:B6 but the first moves the current selection and the second doesn’t.
The following procedure uses Select to explicitly select B2:
Sub ExplicitSelectLoop() 'Loop through cells inputing values. Dim i As Integer Range("B2").Select For i = 1 To 5 Selection.Value = i ActiveCell.Cells(2).Select Next i End Sub
This second procedure uses an object approach, which doesn’t change the selection:
Sub ImplicitObjectLoop() 'Loop through cells inputting values. Dim wkb As Workbook Dim rng As Range Dim i As Integer Set wkb = ActiveWorkbook Set rng = wkb.Worksheets(1).Range("B2") For i = 1 To 5 rng.Offset(i).Value = i Next i End Sub
You might be wondering how the object approach, which I claim is superior, is better when it has more lines of code. How can that be better? The additional lines are declarations; the working code in the For loop is shorter. In fact, you don’t need the declaration statements, but I recommend that you include them anyway.
Eliminating two active selections seems insignificant, but overall, it adds up. The object code is faster and neater because it makes fewer hits on the Range object. The declaration statement defines it once and that’s it.
Implicit is superior
In a short and contrived example, it’s difficult to clearly see the advantages of avoiding Select, and frankly, I wouldn’t recommend that you bother if a quick macro is all you need. However, as you expand your VBA work, you’ll find that avoiding Select makes extended coding faster and easier to maintain.
Send me your questions about Office
I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.
- How to add a drop-down list to an Excel cell (TechRepublic)
- Office Q&A: Juggling multiple indexes in a Word document
- Seven tips to tap into Word’s AutoText power
- Macro trick: How to highlight multiple search strings in a Word document
- 10 Things: How to use Word templates effectively