Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Moving a range
The VBA instructions for moving a range are very similar to those for copying a range, as the
following example demonstrates. The difference is that you use the Cut method instead of the
Copy method. Note that you need to specify only the upper-left cell for the destination range.
The following example moves 18 cells (in A1:C6) to a new location, beginning at cell H1:
Copying a variably sized range
In many cases, you need to copy a range of cells, but you don’t know the exact row and column
dimensions of the range. For example, you might have a workbook that tracks weekly sales, and
the number of rows changes weekly when you add new data.
Figure 11-1 shows a common type of worksheet. This range consists of several rows, and the
number of rows changes each week. Because you don’t know the exact range address at any given
time, writing a macro to copy the range requires additional coding.
Figure 11-1: The number of rows in the data range changes every week.
The following macro demonstrates how to copy this range from Sheet1 to Sheet2 (beginning
at cell A1). It uses the CurrentRegion property, which returns a Range object that
corresponds to the block of cells around a particular cell (in this case, A1).