This topic presents numerous ways to refer to a worksheet range – that is, to get a reference to a Range object. It builds you a solid foundation to write sensible VBA code on working with ranges. Once you get a range referred, you can then use any of the properties and methods of the Range object to work with the range and to get a reference to other Range object. For example, you can treat a Range object as a reference point of navigation. Using the End property of the Range object, you can get a reference to other Range object that represents, say, the next nonempty cell in the worksheet of the range.
A range can be
- A single-area range. This type of range can be a single cell, a rectangular block of cells (also known as a rectangular range) such as the ranges C1:C3 and B2:C5 in a worksheet, one or more contiguous rows, one or more contiguous columns, or all cells in a worksheet.
- A multiple-area range. This type of range consists of two or more areas of single-area ranges – single cells and rectangular blocks of cells. Range(“A1, A1”) and Range(“A2, B2:C5, 3:3”) are examples of two- and three-area ranges, respectively. Yes, the same cell A1 repeated in Range(“A1, A1”) is separately in two different areas.
Below are some commonly used properties and methods to refer to ranges. I will discuss their syntaxes and the ways to use them to refer to ranges in the following topics.
- The Range property of a Worksheet object
- The Range property of a Range object
- The Cells property of a Worksheet object
- The Cells property of a Range object
- The Offset property of a Range object
- The Selection property of the Application object
- The Resize property of a Range object
- The Rows property of a Worksheet object
- The Rows property of a Range object
- The Columns property of a Worksheet object
- The Columns property of a Range object
- The UsedRange property of a Worksheet object
- The CurrentRegion property of a Range object
- The SpecialCells method of a Range object
- The Union method of the Application object
- The Intersect method of the Application object
Note:
Many code fragments in this menu return a Range object (that represents a worksheet range), such as:
Range("C2:B5") 'Returns a Range object that represents the range B2:C5
To see whether it correctly returns a Range object, you can check one of its properties and methods. For example, execute one of statements below in the Immediate window.
? Range("C2:B5").Address 'Returns $B$2:$C$5
Range("C2:B5").Select 'Selects the range B2:C5 in the active sheet
Range("C2:B5").Interior.ColorIndex = 4 'Fills the range B2:C5 green
Refer to ranges
The Range property of a Worksheet object
Range (Cell1)
Range(Cell1, Cell2): Range(“B5”, “C2”); Range(“C2:B5”, “C1:D2”)
The Range property of a Range object
The Cells property of a Worksheet object
The Cells property of a Range object
Cells(rowN, columnN)
Cells(n)
Cells
The Offset property of a Range object
The Selection property of the Application object
The Resize property of a Range object
The Rows property of a Worksheet object
Rows(N)
Rows(“firstRow:lastRow”)
Rows
The Rows property of a Range object
Rows(N)
Rows(“firstRow:lastRow”)
Rows
The Columns property of a Worksheet object
Columns(N)
Columns(“firstColumn:lastColumn”)
Columns
The Columns property of a Range object
Columns(N)
Columns(“firstColumn:lastColumn”)
Columns
The UsedRange property of a Worksheet object
The CurrentRegion property of a Range object
The SpecialCells method of a Range object
The Union method of the Application object
The Intersect method of the Application object
Dissecting Excel VBA 