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 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 non-empty 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 .

Caution

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.

CD-ROM

The companion CD-ROM includes a workbook that demonstrates several common types of range selections. When you open this workbook, named ® range selections. xlsm , the code adds a new menu item to the shortcut menu that appears when you right-click a cell: Selection Demo . This menu contains commands that enable the user to make various types of selections, as shown in Figure 11-2 .

3

? f

C*

+■

4,

I"

¡1

fl

1—

a

Id

i

-r-nr-C

1|

IV

1

V

>

Wi % iri-Wat Ciwi i.^-x-iJ, iwa.t-'CnuCMlin»linwirt

HtrtlMOri f-f^i »-/"' Uin rwM^fflw^NHMLH id4wl Wwl Ie>«*r* bfrfiLrA M* WK

Figure 11-2: This workbook uses a custom shortcut menu to demonstrate how to select variably sized ranges by using VBA.

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

Sub SelectCurrentRegion()

ActiveCell.CurrentRegion.Select End Sub

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