Home > Articles

Referring to Ranges

This chapter is from the book

This chapter is from the book

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:


Using the Cells property with the Range object may seem redundant:


The line refers to the original Range object. But the Cells property has a property, Item, which 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 object is


You must use a numeric value for Row, but you may use the numeric value or string value for Column. Both the following lines refer to cell C5:


Because the Item property is the default property of the Range object, you can shorten these lines to


The ability to use numeric values for parameters is especially 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 are learning to code simply from the recorder, then you might be tempted to write code like

FinalRow = Range("A65536").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 to E is awkward to read and write. But, how else can you do it?

FinalRow = Cells(65536,1).End(xlUp).Row
For i = 1 to FinalRow
  Cells(i,"A").Resize(,5).Font.Bold = True
Next i

Instead of trying to type out the range address, the new code uses the Cells and Resize properties to find the required cell based on the active cell.

Using the Cells Property in the Range Property

You can use Cells properties as parameters in the Range property. The following refers to range A1:E5:


This is especially useful when you need to specify your variables with a parameter, as in the previous looping example.

  • + Share This
  • 🔖 Save To Your Account