Determining the type of selected range

Excel supports several types of range selections:

♦ A contiguous range of cells

♦ One or more entire columns

♦ One or more entire rows

♦ The entire worksheet

♦ Any combination of the above (that is, a multiple selection)

As a result, when your VBA procedure processes a selected range, you can't make any presumptions about what that range might be.

In the case of a multiple range selection, the Range object comprises separate areas. To determine whether a selection is a multiple selection, use the Areas method, which returns an Areas collection. This collection represents all the ranges within a multiple range selection.

You can use an expression like the following to determine whether a selected range has multiple areas:

NumAreas = Selection.Areas.Count

If the NumAreas variable contains a value greater than 1, the selection is a multiple selection.

The AboutRangeSelection procedure uses the AreaType custom function listed here:

Function AreaType(RangeArea As Range) As String ' Returns the type of a range in an area Select Case True

Case RangeArea.Cells.Count = 1

AreaType = "Cell" Case RangeArea.Count = Cells.Count

AreaType = "Worksheet" Case RangeArea.Rows.Count = Cells.Rows.Count

AreaType = "Column" Case RangeArea.Columns.Count = Cells.Columns.Count

AreaType = "Row" Case Else

AreaType = "Block" End Select End Function

This function accepts a Range object as its argument and returns one of five strings that describe the area: Cell, Worksheet, Column, Row, or Block. The function uses a Select Case construct to determine which of five comparison expressions is True. For example, if the range consists of a single cell, the function returns Cell. If the number of cells in the range is equal to the number of cells in the worksheet, it returns Worksheet. If the number of rows in the range equals the number of rows in the worksheet, it returns Column. If the number of columns in the range equals the number of columns in the worksheet, the function returns Row. If none of the Case expressions is True, the function returns Block.

Notice that the comparison doesn't involve absolute numbers. For example, rather than use 65,536 to determine whether the range is a column, it uses Cells.Rows.Count. Because of this, the function works properly even with Excel 5 and Excel 97 (which contain only 16,384 rows). And it will continue to work if Microsoft ever increases the number of rows in a worksheet.

A workbook on the companion CD-ROM contains a procedure (named AboutRangeSelection) that uses the AreaType function to display a message box that describes the current range selection. Figure 11-7 shows an example. Understanding how this routine works will give you a good foundation for working with Range objects.

Figure 11-7: The AboutRangeSelection procedure analyzes the currently selected range.

You might be surprised to discover that Excel allows multiple selections to be identical. For example,if you hold down Ctrl and click five times in cell A1, the selection will have five identical areas. The AboutRangeSelection procedure takes this into account.

Was this article helpful?

0 0

Post a comment