Using the CurrentRegion and UsedRange Properties

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

There are many circumstances where you will write code to work against a range of cells, but at the time you write the code, you will not have information about the range. For example, you might not know the size or location of a range or the location of a cell in relation to another cell. You can use the CurrentRegion and UsedRange properties to work with a range of cells whose size you have no control over. You can use the Offset property to work with cells in relation to other cells where the cell location is unknown.

As shown in the following figure, the Range object's CurrentRegion property returns a Range object representing a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet.

The Ranges Returned by the ActiveCell and CurrentRegion Properties

The CurrentRegion property can return many different ranges on a single worksheet. This property is useful for operations where you must know the dimensions of a group of related cells, but all you know for sure is the location of a cell or cells within the group. For example, when the active cell is inside a table of cells, you could use the following line of code to apply formatting to the entire table:

ActiveCell.CurrentRegion.AutoFormat xlRangeAutoFormatAccounting4

You could also use the CurrentRegion property to return a collection of cells. For example:

Dim rngCurrentCell As Excel.Range

For Each rngCurrentCell In ActiveCell.CurrentRegion.Cells
   ' Work with individual cells here.
Next rngCurrentCell

Every Worksheet object has a UsedRange property that returns a Range object representing the area of a worksheet that is being used. The UsedRange property represents the area described by the farthest upper-left and farthest lower-right nonempty cells in a worksheet and includes all cells in between. For example, imagine a worksheet with entries in only two cells: A1 and G55. The worksheet's UsedRange property would return a Range object containing 385 cells between and including A1 and G55.

You might use the UsedRange property together with the SpecialCells method to return a Range object representing all cells in a worksheet of a specified type. For example, the following code returns a Range object that includes all the cells in the active worksheet that contain a formula:

Dim rngFormulas As Excel.Range
Set rngFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

See Also

Working with Microsoft Excel Objects | Understanding the Range Object | The Range Property | The ActiveCell and Selection Properties | Using the Cells Property | Using the Offset Property