Home > Articles > Home & Office Computing > Microsoft Applications

This chapter is from the book

This chapter is from the book

Using the CurrentRegion Property to Quickly Select a Data Range

CurrentRegion returns a range object representing a set of contiguous data. As long as the data is surrounded by one empty row and one empty column, you can select the table with CurrentRegion:

RangeObject.CurrentRegion

Look at Figure 3.7. The following line would select A1:D3, as this is the contiguous range of cells around cell A1:

Range("A1").CurrentRegion.Select

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

Figure 3.7Figure 3.7 Use CurrentRegion to quickly select a range of contiguous data around the active cell.

Using the SpecialCells Method to Select Specific Cells

Even Excel power users may never 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.8). In the lower-left corner of this dialog is a button called Special. Click that button to get to the super-powerful Go To Special dialog (see Figure 3.9).

Figure 3.8Figure 3.8 While the Go To dialog doesn't seem very useful, press the Special button in the lower-left corner.

Figure 3.9Figure 3.9 The Go To Special dialog has many incredibly useful selection tools.

In the Excel interface, the Go To Special dialog enables you to select only cells with formulas, or only blank cells, or only the visible cells. Selecting visible cells only is excellent for grabbing the visible results of AutoFiltered data.

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

RangeObject.SpecialCells(Type, Value)

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

xlCellTypeAllFormatConditions

xlCellTypeAllValidation

xlCellTypeBlanks

xlCellTypeComments

xlCellTypeConstants

xlCellTypeFormulas

xlCellTypeLastCell

xlCellTypeSameFormatConditions

xlCellTypeSameValidation

xlCellTypeVisible

Value is optional and can be one of the following:

xlErrors

xlLogical

xlNumbers

xlTextValues.

The following returns all the ranges that have conditional formatting set up. It puts 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 consider the data shown in Figure 3.10 to look neat. They enter the Region field only once for each region. This may look aesthetically pleasing, but it is impossible to sort. Even Excel's pivot table routinely returns data in this annoying format.

Figure 3.10Figure 3.10 The blank cells in the region column make data tables such as this very difficult to sort.

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

Sub FillIn()
  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 property returns just the blank cells in that range. Although you can read more about R1C1 style formulas in Chapter 6, "R1C1 Style Formulas," this particular formula fills in all the blank cells with a formula that points to the cell above the blank cell. The second line of code is a fast way to simulate doing a Copy and then Paste Special Values. The result is shown in Figure 3.11.

Figure 3.11Figure 3.11 After running the macro, the blank cells in the region column have been filled in with data from above.




  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus