Image: iStockphoto.com/SolisImages
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.
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
This object accommodates both explicit and implicit selections. To learn more about the Range object's properties and methods, visit Range Properties and Range Methods, respectively.
Explicit selection
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:
Range(reference, [reference]).Select
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.
Table A
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:
Sub ExplicitSelectCell()
'Select a specific cell and enter value.
Range("A1").Select
ActiveCell.Value = "This is A1.
End Sub
Sub ExplicitSelectNonCCells()
'Select noncontiguous cells and set font color to red.
Range("A1, C2, D1, E4").Select
Selection.Font.Color = vbRed
End Sub
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.
Implicit selection
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:
Range("A1").Select
ActiveCell.Value = "This is A1.
with one:
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 question 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 susansalesharkins@gmail.com.
Also read...
Source: http://techrepublic.com.feedsportal.com/c/35463/f/670841/s/4dfbbc00/sc/23/l/0L0Stechrepublic0N0Carticle0Cexcel0Etips0Ehow0Eto0Eselect0Ecells0Eand0Eranges0Eefficiently0Eusing0Evba0C0Tftag0FRSS56d97e7/story01.htm