Excel 2010 Bible

Chapter 44: VBA Examples

889

The example demonstrates that the macro recorder doesn’t always generate the most efficient code.

As you see, you don’t have to select an object to work with it. Note that CopyRange2 doesn’t

select a range; therefore, the active cell doesn’t change when this macro is executed.

Copying a variable-size range

Often, you want to copy a range of cells in which the exact row and column dimensions are

unknown.

Figure 44.1 shows a range on a worksheet. This range contains data that is updated weekly.

Therefore, the number of rows changes. Because the exact range address is unknown at any given

time, writing a macro to copy the range can be challenging.

FIGURE 44.1

This range can consist of any number of rows.

The macro that follows 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 used cells surrounding a particular cell. This is equivalent to choosing Home ➪

Editing ➪ Find & Select ➪ Go To, clicking the Special button, and then selecting the Current

Region option.

Sub CopyCurrentRegion()

Range(“A1”).CurrentRegion.Copy Sheets(“Sheet2”).Range(“A1”)

End Sub

On the CD
A workbook that contains this macro is available on the companion CD-ROM. The file is named
range copy.xlsm.