Referring to ranges

Using the CurrentRegion property to select a data range

CurrentRegion returns a Range object that represents a set of contiguous data. As long as the data is surrounded by one empty row and one empty column, you can select the data set by using CurrentRegion:


The following line selects A1:D3 because this is the contiguous range of cells around cell A1 (see Figure 3-5):


This is useful if you have a data set whose size is in constant flux.


FIGURE 3-5 You can use CurrentRegion to select a range of contiguous data around the active cell.

Case Study: Using the SpecialCells method to select specific cells

Even Excel power users might not have encountered the Go To Special dialog box. If you press the F5 key in an Excel worksheet, you get the normal Go To dialog box (see Figure 3-6). In the lower-left corner of this dialog box is a button labeled Special. Click this button to get to the super-powerful Go To Special dialog box (see Figure 3-7).


FIGURE 3-6 Although the Go To dialog box doesn’t seem useful, click the Special button in the lower-left corner to specify what type of cells to select.

In the Excel interface, the Go To Special dialog box enables you to select only cells with formulas, only blank cells, or only the visible cells. Selecting only visible cells is excellent for grabbing the visible results of AutoFiltered data. If you already have a range highlighted, only cells within this range meeting the criteria will be selected. Make sure only one cell is selected to search the entire sheet.

To simulate the Go To Special dialog box in VBA, use the SpecialCells method. This enables you to act on cells that meet certain criteria, like this:

RangeObject.SpecialCells(Type, Value)

FIGURE 3-7 The Go To Special dialog box has many incredibly useful selection tools, such as one for selecting only the formulas on a sheet.

The SpecialCells method has two parameters: Type and Value. Type is one of the xlCellType constants:


Set one of the following optional Value constants if you use xlCellTypeConstants or xlCellTypeFormulas:


The following code returns all the ranges that have conditional formatting. It produces an error if there are no conditional formats and adds a border around each contiguous section it finds:

Set rngCond = ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions)
If Not rngCond Is Nothing Then
 rngCond.BorderAround xlContinuous
End If

Have you ever had someone send you a worksheet without all the labels filled in? Some people think that the data shown in Figure 3-8 looks tidy. They enter the Region field only once for each region. This might look aesthetically pleasing, but it’s impossible to sort.


FIGURE 3-8 The blank cells in the Region column make it difficult to sort data sets such as this.

Using the SpecialCells method to select all the blanks in this range is one way to fill the blank region cells quickly using the region found above them:

Sub FillIn()
On Error Resume Next 'Need this because if there aren't any blank
'cells, the code will error
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 _
 = "=R[-1]C"
Range("A1").CurrentRegion.Value = Range("A1").CurrentRegion.Value
End Sub

In this code, Range("A1").CurrentRegion refers to the contiguous range of data in the report. The SpecialCells method returns just the blank cells in that range. This particular formula fills in all the blank cells with a formula that points to the cell above the blank cell. (You can read more about R1C1-Style Formulas in Chapter 5, “R1C1-style formulas.”) The second line of code is a fast way to simulate using the Copy and Paste Special Values commands. Figure 3-9 shows the results.


FIGURE 3-9 After the macro runs, the blank cells in the Region column have been filled with data.