## Copying a variablesized range

In many cases, you need to copy a range of cells but don't know the exact row and column dimensions. For example, you might have a workbook that tracks weekly sales. The number of rows changes as you add new data.

Figure 14-1 shows a range on a worksheet. This range consists of several rows, and the number of rows can change from day to day. Because you don't know the exact range address at any given time, writing a macro to copy the range can be challenging. Are you up for the challenge?

Figure 14-1:

This range can consist of any number of rows.

 ffl exämple&.xls i- jy ä A B fi D E F — 1 Date Units Amount 2 ® \$2,737 '3 2-Jan 143 (154 4: Ä <(109. 5 169 1614 6 5-Jan 102 42,744 7 6-Jar} 143 15,164 fl 7-Jan 109 \$4,314 9 8-Jan 122 \$4,440 W 9-Jai 156. \$4,657 11 10-Jan 187 \$6';989 12 ÜJan 140 5S,fll4 :■ 12-Jan 132 ■p ,070 14 v | 4 > .HfeSheetL-Xdaily/ j< llll RI

The following macro demonstrates how to copy this range from Sheetl to Sheet2 (beginning at cell Al). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of cells around a particular cell. In this case, that's Al.

 Sub CopyCurrentRegion() Range("A1").CurrentRegion ■ Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet1").Select Application.CutCopyMode = False End Sub

Using the CurrentRegion property is equivalent to choosing the EditOGo To command, clicking the Special button, and selecting the Current region option. To see how this works, record your actions while issuing that command. Generally, the CurrentRegion consists of a rectangular block of cells surrounded by one or more blank rows or columns.

You can make this macro even more efficient by not selecting the destination. The following macro takes advantage of the fact that the Copy method can use an argument for the destination range:

Sub CopyCurrentRegion2()

Range("A1").CurrentRegion.Copy _

Sheets("Sheet2").Range("A1") Application.CutCopyMode = False End Sub

0 0