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

Post a comment