Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Selecting or otherwise identifying various types of ranges
Much of the work that you’ll 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:
Here’s a similar example that uses a specific cell as the starting point:
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 with the ActiveCell property. 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, 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.
The following macro is in the example workbook. The SelectCurrentRegion macro simulates