| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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 may 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. For more information about the Offset property, see "Using the Offset Property" later in this chapter.
As shown in Figure 5.2, 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.
Figure 5.2 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 need to 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 on 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 on 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)
If you examine the sample code in the ExcelSamples.xls file in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM, you will find many procedures that use the CurrentRegion and UsedRange properties to define ranges.