Referring to ranges

By Bill Jelen and Tracy Syrstad
3/11/2022

Contents

A range in Excel can be a cell, a row, a column, or a grouping of any of these. In this sample chapter from Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365), you will explore 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.

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 `Range` object

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!

Syntax for specifying a range

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

Referencing named ranges

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.