The VBA Range object is the most heavily-used VBA object type in any macro out there. After you record some macros, it will become extremely obvious how often you will use the Range object in Excel VBA. The main reason is that the Range object represents the cells in your worksheet, which we all know is the livelihood of Excel workbooks. There are so many things to talk about with Excel Ranges and originally I had a very, very long post about this topic. However, I decided to try to keep this as simple as possible and I’ll break up this post into several other posts to make them more consumable. I hope you enjoy this series of articles.
Also, if you’re not too familiar with VBA, please see my VBA for Beginners post.
What is a Range?
Think of a Range like a single cell or set of cells. The word “Range” might even be short for “Cell Range” because it represents a collection (i.e. range) of cells.
A range can be a single cell, multiple cells on the same worksheet, or even a set of 3D cells.
A Simple Example
If you’ve ever recorded a macro before, then you have inevitably run across the Range object in VBA code.
Here’s a quick example. I recorded a macro that did the following:
Here’s a breakdown of what I did:
- Select cell
"Hi"and press enter
- Select cell
- Used the Fill Handle to copy cell
B1’s value down to cell
- Pressed Stop Recording
Here’s the code that macro created for us:
Sub Macro1() ' ' Macro1 Macro ' ' Range("B1").Select ActiveCell.FormulaR1C1 = "Hi" Range("B1").Select Selection.AutoFill Destination:=Range("B1:B11"), Type:=xlFillDefault Range("B1:B11").Select End Sub
As you can see in the code, it follows the steps that I took in the Macro Recorder after I pressed OK to start recording:
|Used the Fill Handle to copy cell
Let’s focus on the
Range("B1").Select part of the code. How does this work?
The part of the code
Range("B1") is a built-in function that tells Excel
“please give me this Range.” After that, we can do different things with that
range such as Select it (as shown in this example), add a formula to it, format
Using the VBA Range Function
The Range function has a couple of different ways to use it that we’ll cover quickly.
The first method to using Ranges that we’ll discuss is the following:
_arg_ is the name of the range. This is a string representation that can
be any of the following:
- A range reference, like
A1:B10, or multiple cell collections like
- A named range, like
- Note that you cannot use R1C1 notation in the string here (more on this later).
A simple example is from the above, such as
Range(_arg_) is actually shorthand for
meaning that anytime you use
Range(_arg_) you are referring to the active
worksheet. You can change this behavior by specifying the worksheet you want to
use, such as
Worksheets("MyWorksheet").Range("A1") so that no matter what
sheet is selected, you will always use the
A1 range in MyWorksheet.
In this method, you can pass in two other ranges or two objects that represent
Each Range parameter inside the main Range function represents any cell or set of cells. The easiest way to think of this is that each Range parameter represents a corner of the entire range that you want to represent. When you lay out the two corners, the overall range will be everything between those two corners.
||This will select cells
||This will select from the
Side note: in an Excel formula, you can actually put ranges like this. If you put
=Sum(B10:A1)Excel will happily accept that, but it will change your formula to
Range(Range, Range) function is shorthand for
Objects that are the VBA Range Type
There are some objects in VBA that are actually of the Range type.
|VBA Range Object||Definition|
||A Range object that represents your entire cell selection. If you selected cells
Where to go from here
Again, I wanted to keep this post short and to the point by giving an intro to the VBA Range object. From here, I recommend playing around with some of your existing macros, or create some new ones. Try to change the macros by doing one of the following:
- Change any
- Change any
Experiment and see what works and what doesn’t work. I’ll be back with more
details on more VBA Range functions, like the
Worksheet.Cells function and the