In this chapter, you will:
Learn how to reference the Range object
Reference ranges in other sheets
Reference a range relative to another range
Use the Cells
property to select a range
Use the Offset
property to refer to a range
Use the Resize
property to change the size of a range
Use the Columns
and Rows
properties to specify a range
Use the Union
method to join multiple ranges
Use the Intersect
method to create a new range from overlapping ranges
Use the IsEmpty
function to check whether a cell is empty
Use the CurrentRegion
property to select a data range
Use the SpecialCells
property to interact with specific cells in a range
Use the Areas
collection to return a noncontiguous range
Learn the syntax used for tables
A range can be a cell, a row, a column, or a grouping of any of these. The Range
object is probably the most frequently used object in Excel VBA; after all, you’re manipulating data on a sheet. Although a range can refer to any grouping of cells on a sheet, it can refer to only one sheet at a time. If you want to refer to ranges on multiple sheets, you must refer to each sheet separately.
This chapter shows you different ways of referring to ranges, such as specifying a row or column. You’ll also find out how to manipulate cells based on the active cell and how to create a new range from overlapping ranges.
The following is the Excel object hierarchy:
Application > Workbook > Worksheet > Range
The Range
object is a property of the Worksheet
object. This means it requires that a sheet be active or else it must reference a worksheet. Both of the following lines mean the same thing if Worksheets(1) is the active sheet:
Range("A1")
Worksheets(1).Range("A1")
There are several ways to refer to a Range
object. Range("A1")
is the most identifiable because that is how the macro recorder refers to it. However, all the following are equivalent when referring to cell D5:
Range("D5")
[D5]
Range("B3").Range("C3")
Cells(5,4)
Range("A1").Offset(4,3)
Range("MyRange") 'assuming that D5 has a 'Name of MyRange
Which format you use depends on your needs. Keep reading. It will all make sense soon!
The Range
property has two acceptable syntaxes. To specify a rectangular range in the first syntax, specify the complete range reference just as you would in a formula in Excel:
Range("A1:B5")
In the alternative syntax, specify the upper-left corner and lower-right corner of the desired rectangular range. In this syntax, the equivalent statement might be this:
Range("A1", "B5")
For either corner, you can substitute a named range, the Cells
property, or the ActiveCell
property. The following line of code selects the rectangular range from A1 to the active cell:
Range("A1", ActiveCell).Select
The following statement selects from the active cell to five rows below the active cell and two columns to the right:
Range(ActiveCell, ActiveCell.Offset(5, 2)).Select
You probably have already used named ranges on your worksheets and in formulas. You can also use them in VBA.
Use the following code to refer to the range "MyRange"
in Sheet1:
Worksheets("Sheet1").Range("MyRange")
Notice the name of the range is in quotes—unlike the use of named ranges in formulas on the sheet itself. If you forget to put the name in quotes, Excel thinks you are referring to a variable in the program. One exception is if you use the shortcut syntax discussed in the next section. In that case, quotes aren’t used.
A shortcut is available when referencing ranges. The shortcut involves using square brackets, as shown in Table 3-1.
Standard Method |
Shortcut |
---|---|
|
|
|
|
|
|
|
|
Switching between sheets by activating the needed sheet slows down your code. To avoid this, refer to a sheet that is not active by first referencing the Worksheet
object:
Worksheets("Sheet1").Range("A1")
This line of code references Sheet1 of the active workbook even if Sheet2 is the active sheet.
To reference a range in another workbook, include the Workbook
object, the Worksheet
object, and then the Range
object:
Workbooks("InvoiceData.xlsx").Worksheets("Sheet1").Range("A1")
To use the Range
property as an argument within another Range
property, identify the range fully each time. For example, suppose that Sheet1 is your active sheet and you need to total data from Sheet2:
WorksheetFunction.Sum(Worksheets("Sheet2").Range(Range("A1"), _
Range("A7")))
This line does not work. Why not? Although Range("A1"), Range("A7") is meant to refer to the sheet at the beginning of the code line (Sheet2), Excel does not assume that you want to carry the Worksheet
object reference over to these other Range
objects and assumes that they refer to the active sheet, Sheet1. So what do you do? Well, you could write this:
WorksheetFunction.Sum(Worksheets("Sheet2").Range(Worksheets("Sheet2"). _
Range("A1"), Worksheets("Sheet2").Range("A7")))
But this not only is a long line of code but also difficult to read! Thankfully, there is a simpler way, using With...End With
:
With Worksheets("Sheet2")
WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A7")))
End With
Notice now there is a .Range in your code but without the preceding object reference. That’s because With Worksheets("Sheet2") implies that the object of the range is that worksheet. Whenever Excel sees a period without an object reference directly to the left of it, it looks up the code for the closest With
statement and uses that as the object reference.
Typically, the Range
object is a property of a worksheet. It is also possible to have Range
be the property of another range. In this case, the Range
property is relative to the original range, which makes for unintuitive code. Consider this example:
Range("B5").Range("C3").Select
This code actually selects cell D7. Think about cell C3, which is located two rows below and two columns to the right of cell A1. The preceding line of code starts at cell B5. If we assume that B5 is in the A1 position, VBA finds the cell that would be in the C3 position relative to B5. In other words, VBA finds the cell that is two rows below and two columns to the right of B5, which is D7.
Again, I consider this coding style to be very unintuitive. This line of code mentions two addresses, and the actual cell selected is neither of these addresses! It seems misleading when you’re trying to read this code.
You might consider using this syntax to refer to a cell relative to the active cell. For example, the following line of code activates the cell three rows down and four columns to the right of the currently active cell:
Selection.Range("E4").Select
I mention this syntax only because the macro recorder uses it. Recall that when you recorded a macro in Chapter 1, “Unleashing the power of Excel with VBA,” with relative references on, the following line was recorded:
ActiveCell.Offset(0, 4).Range("A2").Select
This line found the cell four columns to the right of the active cell, and from there it selected the cell that would correspond to A2. This is not the easiest way to write code, but it is the way the macro recorder does it.
Although a worksheet is usually the object of the Range
property, occasionally, such as during recording, a range may be the property of a range.
Cells
property to select a rangeThe Cells
property refers to all the cells of the specified Range
object, which can be a worksheet or a range of cells. For example, this line selects all the cells of the active sheet:
Cells.Select
Using the Cells
property with the Range
object might seem redundant:
Range("A1:D5").Cells
This line refers to the original Range
object. However, the Cells
property has an Item
property that makes the Cells
property very useful. The Item
property enables you to refer to a specific cell relative to the Range object.
The syntax for using the Item
property with the Cells
property is as follows:
Cells.Item(Row,Column)
You must use a numeric value for Row
, but you may use the numeric value or string value for Column
. Both of the following lines refer to cell C5:
Cells.Item(5,"C")
Cells.Item(5,3)
Because the Item
property is the default property of the Range
object, you can shorten these lines as follows:
Cells(5,"C")
Cells(5,3)
The ability to use numeric values for parameters is particularly useful if you need to loop through rows or columns. The macro recorder usually uses something like Range("A1").Select
for a single cell and Range("A1:C5").Select
for a range of cells. If you’re learning to code only from the recorder, you might be tempted to write code like this:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 to FinalRow
Range("A" & i & ":E" & i).Font.Bold = True
Next i
This little piece of code, which loops through rows and bolds the cells in columns A through E, is awkward to read and write. But how else can you do it? Like this:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 to FinalRow
Cells(i,"A").Resize(,5).Font.Bold = True
Next i
Instead of trying to type the range address, the new code uses the Cells
and Resize
properties to find the required cell, based on the active cell. See the “Using the Resize
property to change the size of a range” section later in this chapter for more information on the Resize
property.
You can use the Cells
properties for parameters in the Range
property. The following refers to the range A1:E5:
Range(Cells(1,1),Cells(5,5))
This is particularly useful when you need to specify variables with a parameter, as in the previous looping example.
Offset
property to refer to a rangeYou’ve already seen a reference to Offset
when you recorded a relative reference. Offset
enables you to manipulate a cell based on the location of another cell, such as the active cell. Therefore, you do not need to know the address of the cell you want to manipulate.
The syntax for the Offset
property is as follows:
Range.Offset(RowOffset, ColumnOffset)
For example, the following code affects cell F5 from cell A1:
Range("A1").Offset(RowOffset:=4, ColumnOffset:=5)
Or, shorter yet, you can write this:
Range("A1").Offset(4,5)
The count of the rows and columns starts at A1 but does not include A1.
If you need to go over only a row or a column, but not both, you don’t have to enter both the row and the column parameters. To refer to a cell one column over, use one of these lines:
Range("A1").Offset(ColumnOffset:=1)
Range("A1").Offset(,1)
Both of these lines have the same meaning, so the choice is yours. If you use the second line, make sure to include the comma so Excel knows that the 1 refers to the ColumnOffset
argument. Referring to a cell one row up is similar:
Range("B2").Offset(RowOffset:=-1)
Range("B2").Offset(-1)
Once again, you can choose which one to use. It’s a matter of readability of the code.
Suppose you have a list of produce in column A, with totals next to the produce items in column B. If you want to find any total equal to zero and place LOW
in the cell next to it, do this:
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _
LookIn:=xlValues)
Rng.Offset(, 1).Value = "LOW"
When used in a Sub
and looping through a data set, it would look like this:
Sub FindLow()
With Range("B1:B16")
Set Rng = .Find(What:="0", LookAt:=xlWhole, LookIn:=xlValues)
If Not Rng Is Nothing Then
firstAddress = Rng.Address
Do
Rng.Offset(, 1).Value = "LOW"
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> firstAddress
End If
End With
End Sub
The LOW
totals are noted by the program, as shown inFigure 3-1.
Note Refer to the section “Object variables” in Chapter 4, “Looping and flow control,” for more information on the Set
statement.
Offsetting isn’t only for single cells; you can use it with ranges. You can shift the focus of a range over in the same way you can shift the active cell. The following line refers to B2:D4 (seeFigure 3-2):
Range("A1:C3").Offset(1,1)
Resize
property to change the size of a rangeThe Resize
property enables you to change the size of a range based on the location of the active cell. You can create a new range as needed. This is the syntax for the Resize
property:
Range.Resize(RowSize, ColumnSize)
To reference the range B3:D13, use the following:
Range("B3").Resize(RowSize:=11, ColumnSize:=3)
Here’s a simpler way to reference this range:
Range("B3").Resize(11, 3)
But what if you need to resize by only a row or a column—not both? You don’t have to enter both the row and the column parameters.
To expand by two columns, use either of the following:
Range("B3").Resize(ColumnSize:=2)
or
Range("B3").Resize(,2)
Both lines mean the same thing. The choice is yours. If you use the second line, make sure to include the comma so Excel knows the 2 refers to the ColumnSize
argument. Resizing just the rows is similar. You can use either of the following:
Range("B3").Resize(RowSize:=2)
or
Range("B3").Resize(2)
Once again, the choice is yours. It is a matter of readability of the code.
From the list of produce, say that you want to find the zero totals and color the cells of the total and corresponding produce (seeFigure 3-3). Here’s what you do:
Set Rng = Range("B1:B16").Find(What:="0", LookAt:=xlWhole, _
LookIn:=xlValues)
Rng.Offset(, -1).Resize(, 2).Interior.ColorIndex = 15
Notice that the Offset
property first moves the active cell over to the produce column. When you’re resizing, the upper-left-corner cell must remain the same.
Resizing isn’t only for single cells; you can use it to resize an existing range. For example, if you have a named range but need it and the column next to it, use this:
Range("Produce").Resize(,2)
Remember, the number you resize by is the total number of rows/columns you want to include.
Columns
and Rows
properties to specify a rangeThe Columns
and Rows
properties refer to the columns and rows of a specified Range
object, which can be a worksheet or a range of cells. They return a Range
object referencing the rows or columns of the specified object.
You’ve seen the following line used, but what is it doing?
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
This line of code finds the last row in a sheet in which column A has a value and places the row number of that Range
object into the variable called FinalRow
. This can be useful when you need to loop through a sheet row by row; you will know exactly how many rows you need to go through.
Note Some properties of columns and rows require contiguous rows and columns in order to work properly. For example, if you were to use the following line of code, 9 would be the answer because only the first range would be evaluated:
Range("A1:B9, C10:D19").Rows.Count
However, if the ranges were grouped separately, the answer would be 19. Excel takes the top-left cell address, A1, and the bottom-right cell address, D19, and counts the rows in the range A1:D19:
Range("A1:B9", "C10:D19").Rows.Count
Union
method to join multiple rangesThe Union
method enables you to join two or more noncontiguous ranges. It creates a temporary object of the multiple ranges, which enables you to affect them at the same time:
Application.Union(argument1, argument2, etc.)
The expression Application
is not required. The following code joins two named ranges on the sheet, inserts the =RAND()
formula, and bolds them:
Set UnionRange = Union(Range("Range1"), Range("Range2"))
With UnionRange
.Formula = "=RAND()"
.Font.Bold = True
End With
Intersect
method to create a new range from overlapping rangesThe Intersect
method returns the cells that overlap between two or more ranges. If there is no overlap, an error is returned:
Application.Intersect(argument1, argument2, etc.)
The expression Application
is not required. The following code colors the overlapping cells of the two ranges:
Set IntersectRange = Intersect(Range("Range1"), Range("Range2"))
IntersectRange.Interior.ColorIndex = 6
IsEmpty
function to check whether a cell is emptyThe IsEmpty
function returns a Boolean value that indicates whether a single cell is empty: True
if empty, False
if not. The cell must truly be empty for the function to return True
. If it contains even just a space that you cannot see, Excel does not consider the cell to be empty:
IsEmpty(Cell)
Say that you have several groups of data separated by a blank row. You want to make the separations a little more obvious. The following code goes down the data in column A. When it finds an empty cell in column A, it colors in the first four cells of that row (seeFigure 3-4):
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
If IsEmpty(Cells(i, 1)) Then
Cells(i, 1).Resize(1, 4).Interior.ColorIndex = 1
End If
Next i
CurrentRegion
property to select a data rangeCurrentRegion
returns a Range
object that represents a set of contiguous data. As long as the data is surrounded by one empty row and one empty column, you can select the data set by using CurrentRegion
:
RangeObject.CurrentRegion
The following line selects A1:D3 because this is the contiguous range of cells around cell A1 (seeFigure 3-5):
Range("A1").CurrentRegion.Select
This is useful if you have a data set whose size is in constant flux.
Case Study: Using the SpecialCells
method to select specific cells
Even Excel power users might not have encountered the Go To Special dialog box. If you press the F5 key in an Excel worksheet, you get the normal Go To dialog box (seeFigure 3-6). In the lower-left corner of this dialog box is a button labeled Special. Click this button to get to the super-powerful Go To Special dialog box (seeFigure 3-7).
In the Excel interface, the Go To Special dialog box enables you to select only cells with formulas, only blank cells, or only the visible cells. Selecting only visible cells is excellent for grabbing the visible results of AutoFiltered data.
To simulate the Go To Special dialog box in VBA, use the SpecialCells
method. This enables you to act on cells that meet certain criteria, like this:
RangeObject.SpecialCells(Type, Value)
The SpecialCells
method has two parameters: Type
and Value
. Type
is one of the xlCellType
constants:
xlCellTypeAllFormatConditions
xlCellTypeAllValidation
xlCellTypeBlanks
xlCellTypeComments
xlCellTypeConstants
xlCellTypeFormulas
xlCellTypeLastCell
xlCellTypeSameFormatConditions
xlCellTypeSameValidation
xlCellTypeVisible
Set one of the following optional Value
constants if you use xlCellTypeConstants
or xlCellTypeFormulas
:
xlErrors
xlLogical
xlNumbers
xlTextValues
The following code returns all the ranges that have conditional formatting. It produces an error if there are no conditional formats and adds a border around each contiguous section it finds:
Set rngCond = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)
If Not rngCond Is Nothing Then
rngCond.BorderAround xlContinuous
End If
Have you ever had someone send you a worksheet without all the labels filled in? Some people think that the data shown inFigure 3-8 looks tidy. They enter the Region field only once for each region. This might look aesthetically pleasing, but it’s impossible to sort.
Using the SpecialCells
method to select all the blanks in this range is one way to fill the blank region cells quickly using the region found above them:
Sub FillIn()
On Error Resume Next 'Need this because if there aren't any blank
'cells, the code will error
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
= "=R[-1]C"
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
End Sub
In this code, Range("A1").CurrentRegion
refers to the contiguous range of data in the report. The SpecialCells
method returns just the blank cells in that range. This particular formula fills in all the blank cells with a formula that points to the cell above the blank cell. (You can read more about R1C1-Style Formulas in Chapter 5, “R1C1-style formulas.”) The second line of code is a fast way to simulate using the Copy and Paste Special Values commands.Figure 3-9 shows the results.
Areas
collection to return a noncontiguous rangeThe Areas
collection is a collection of noncontiguous ranges within a selection. It consists of individual Range
objects representing contiguous ranges of cells within the selection. If a selection contains only one area, the Areas
collection contains a single Range
object that corresponds to that selection.
You might be tempted to loop through the rows in a sheet and check the properties of a cell in a row, such as its formatting (for example, font or fill) or whether the cell contains a formula or value. Then you could copy the row and paste it to another section. However, there is an easier way. InFigure 3-10, the user enters the values below each fruit and vegetable. The percentages are formulas. The following line of code selects the cells with numeric constants and copies them to another area:
Range("A:D").SpecialCells(xlCellTypeConstants, xlNumbers).Copy _
Range("I1")
A table is a special type of range that offers the convenience of referencing named ranges. However, tables are not created in the same manner as other ranges. For more information on how to create a named table, see Chapter 6, “Creating and manipulating names in VBA.”
Although you can reference a table by using Worksheets(1).Range("Table1")
, you have access to more of the properties and methods that are unique to tables if you use the ListObjects
object, like this:
Worksheets(1).ListObjects("Table1")
This opens the properties and methods of a table, but you can’t use that line to select the table. To do that, you have to specify the part of the table you want to work with. To select the entire table, including the header and total rows, specify the Range
property:
Worksheets(1).ListObjects("Table1").Range.Select
The table part properties include the following:
Range
—Returns the entire table.
DataBodyRange
—Returns the data part only.
HeaderRowRange
—Returns the header row only.
TotalRowRange
—Returns the total row only.
What I really like about coding with tables is the ease of referencing specific columns of a table. You don’t have to know how many columns to move in from a starting position or the letter/number of the column, and you don’t have to use a FIND function. Instead, you can use the header name of the column. For example, to select the data of the Qty column of the table, but not the header or total rows, do this:
Worksheets(1).ListObjects("Table1").ListColumns("Qty")_
.DataBodyRange.Select
Note For more details on coding with tables, check out Excel Tables: A Complete Guide for Creating, Using, and Automating Lists and Tables by Zack Barresse and Kevin Jones (ISBN: 9781615470280).
Chapter 4 describes a fundamental component of any programming language: loops. If you have taken a programming class, you will be familiar with basic loop structures. VBA supports all the usual loops. That chapter also describes a special loop, For Each...Next
, which is unique to object-oriented programming such as VBA.