Selecting or otherwise identifying various types of ranges

Much of the work that you will do in VBA will involve working with ranges — either selecting a range or identifying a range so that you can do something with the cells.

In earlier versions of Excel, recording a macro that selects cells (such as pressing Ctrl+Shift+—was a hit or miss proposition. Beginning with Excel 2002, the macro recorder seems to handle these types of selections much better than in previous versions. However, it's always a good idea to check your recorded code very carefully to make sure that the selection code works as you intended.

In addition to the CurrentRegion property (which I discussed earlier), you should also be aware of the End method of the Range object. The End method takes one argument, which determines the direction in which the selection is extended. The following statement selects a range from the active cell to the last nonempty cell:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

As you might expect, three other constants simulate key combinations in the other directions: xlUp, xlToLeft, and xlToRight.

Be careful when using the End method. If the active cell is at the perimeter of a range or if the range contains one or more empty cells,the End method may not produce the desired results.

The companion CD-ROM includes a workbook that demonstrates several common types of range selections. When you open this workbook, you'll see a new menu command: Selection Demo. This menu contains commands that enable the user to make various types of selections, as shown in Figure 11-2.

The following macro is in the example workbook. The SelectCurrentRegion macro simulates pressing Ctrl+Shift+*.

Sub SelectCurrentRegion()

ActiveCell.CurrentRegion.Select End Sub

Figure 11-2: This workbook demonstrates how to select variably sized ranges by using VBA.

Often, you won't want to actually select the cells. Rather, you'll want to work with them in some way (for example, format them). The cell-selecting procedures can easily be adapted. The following procedure was adapted from SelectCurrentRegion. This procedure doesn't select cells; it applies formatting to the range that's defined as the current region around the active cell. The other procedures in the example workbook can also be adapted in this manner.

Sub FormatCurrentRegion()

ActiveCell.CurrentRegion.Font.Bold = True End Sub

0 0

Post a comment