- The Range Object
- Syntax to Specify a Range
- Named Ranges
- Shortcut for Referencing Ranges
- Referencing Ranges in Other Sheets
- Referencing a Range Relative to Another Range
- Use the Cells Property to Select a Range
- Use the Offset Property to Refer to a Range
- Use the Resize Property to Change the Size of a Range
- Use the Columns and Rows Properties to Specify a Range
- Use the Union Method to Join Multiple Ranges
- Use the Intersect Method to Create a New Range from Overlapping Ranges
- Use the ISEMPTY Function to Check Whether a Cell Is Empty
- Use the CurrentRegion Property to Select a Data Range
- Use the Areas Collection to Return a Noncontiguous Range
- Referencing Tables
- Next Steps
From the author of
From the author of
From the author of
Use the CurrentRegion Property to 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
The following line selects A1:D3 because this is the contiguous range of cells around cell A1 (see Figure 3.5):
){kind=link}
Range("A1").CurrentRegion.Select
Figure 3.5. Use CurrentRegion to select a range of contiguous data around the active cell.
This is useful if you have a table whose size is in constant flux.
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 is a button labeled Special. Click that button to get to the superpowerful Go To Special dialog box (see Figure 3.7).
)
Figure 3.6. Although the Go To dialog doesn’t seem useful, click the Special button in the lower-left corner.
)
Figure 3.7. The Go To Special dialog has many incredibly useful selection tools, such as selecting only the formulas on a sheet.
In the Excel interface, the Go To Special dialog 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.
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 code returns all the ranges that have conditional formatting set up. 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 consider that the data shown in Figure 3.8 looks neat. They enter the Region field only once for each region. This might look aesthetically pleasing, but it is impossible to sort.
){kind=link}
Figure 3.8. The blank cells in the Region column make it difficult to sort data tables such as this.
Using the SpecialCells method to select all the blanks in this range is one way to fill in all the blank region cells quickly with 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. Although you can read more about R1C1-style formulas in Chapter 5, “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. Figure 3.9 shows the results.
){kind=link}
Figure 3.9. After the macro runs, the blank cells in the Region column have been filled in with data.