Referring to ranges
- By Bill Jelen and Tracy Syrstad
- 3/11/2022
- The Range object
- Referencing ranges in other sheets
- Referencing a range relative to another range
- Using the Cells property to select a range
- Using the Offset property to refer to a range
- Using the Resize property to change the size of a range
- Using the Columns and Rows properties to specify a range
- Using the Union method to join multiple ranges
- Using the Intersect method to create a new range from overlapping ranges
- Using the IsEmpty function to check whether a cell is empty
- Using the CurrentRegion property to select a data range
- Using the Areas collection to return a noncontiguous range
- Referencing tables
- Next steps
Using the Cells property to select a range
The 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.