Referring to ranges

  • 3/11/2022

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.

Shortcut for referencing ranges

A shortcut is available when referencing ranges. The shortcut involves using square brackets, as shown in Table 3-1.

TABLE 3-1 Shortcuts for referencing ranges

Standard Method

Shortcut

Range("D5")

[D5]

Range("A1:D5")

[A1:D5]

Range("A1:D5, G6:I17")

[A1:D5, G6:I17]

Range("MyRange")

[MyRange]