Working With Variable-Sized Ranges
In many situations, your VBA procedure needs to work with a range that can vary in size. For example, you may have a worksheet that holds weekly sales data. Every week you add a new row to it. Creating a VBA procedure to manipulate the data can be tricky, since the size of the range will vary from week to week. This document describes a number of VBA techniques that are useful when working with ranges that have an unknown size.
The CurrentRegion Property
If you need to work with an entire range of cells (such as a worksheet database, or list), the CurrentRegion property will be useful. This property returns a range object. To understand how this works, experiment with the Current Region option in the Go To Special dialog box (press F5, then click Special).
The statement below selects the current region for cell A1.
Range("A1").CurrentRegion.Select
The End Property
You are probably familiar with Excel's keystrokes that let you move or select to the end of a row or column. For example, to move the cell pointer to the last nonblank cell in a column, you press Ctrl+Down Arrow (or, End followed by Down Arrow). To select cells from the active cell down to the first blank cell in the column, you press Ctrl+Shift+Down Arrow (or, End followed by Shift+Down Arrow).
If you record a VBA macro using these keystrokes, you may be surprised that the result isn't what you expect. Rather the record code to simulate these key combinations, the recorder simply records the actual cell addresses. However, VBA contains a useful property (End) that provides the equivalent of this type of action. The statement below selects cells from the active cell down to the last non-empty cell in the column.
Range(ActiveCell, ActiveCell.End(xlDown)).Select
In this case, xlDown is a built-in constant. As you may expect, there are three other constants to simulate key combinations in the other directions: xlUp, xlToLeft and xlToRight.
Download An Example
I developed a workbook that demonstrates how to make a wide variety of variable-range selections. You can download rangesel.xls and view the VBA code to make the following types of selections:
- Select Down (Like Ctrl+Shift+Down)
- Select Up (Like Ctrl+Shift+Up)
- Select To Right (Like Ctrl+Shift+Right)
- Select To Left (Like Ctrl+Shift+Left)
- Select Current Region (Like Ctrl+Shift+*)
- Select Active Area (Like End, Home, Ctrl+Shift+Home)
- Select Contiguous Cells in ActiveCell's Column
- Select Contiguous Cells in ActiveCell's Row
- Select an Entire Column (Like Ctrl+Spacebar)
- Select an Entire Row (Like Shift+Spacebar)
- Select the Entire Worksheet (Like Ctrl+A)
- Select the Next Blank Cell Below
- Select the Next Blank Cell To the Right
- Select From the First NonBlank to the Last Nonblank in the Row
- Select From the First NonBlank to the Last Nonblank in the Column