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 some additional coding.

A

0

c

0

2

Week

Total Sales

Iievt Cu-Hoiners I

L

31,454

12

3

2

2B.&2Q

16

J

3

ASM2

31

5

4

30 562

20

6

i

17,345

13

7

6

3s,eoo

25

S

7

36,732

31

9

10

-

H

* H Stveetl

nm----!►.-:■

Figure 11-1: The number of rows in the data range changes every week.

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).

Sub CopyCurrentRegion2()

Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1") End Sub

Tips for Working with Ranges

When you work with ranges, keep the following points in mind:

■ Your code doesn't need to select a range in order to work with it.

■ If your code does select a range, its worksheet must be active. You can use the Activate method of the Worksheets collection to activate a particular sheet.

■ The macro recorder doesn't always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient.

■ It's a good idea to use named ranges in your VBA code. For example, referring to Range("Total") is better than Range("D45") . In the latter case, if you add a row above row 45, the cell address will change. You would then need to modify the macro so that it uses the correct range address (D46).

■ If you rely on the macro recorder when selecting ranges, make sure that you record the macro using relative references. Use the Developer' » Code Use Relative References control.

■ When running a macro that works on each cell in the current range selection, the user might select entire columns or rows. In most cases, you don't want to loop through every cell in the selection. Your macro should create a subset of the selection consisting of only the nonblank cells. See "Looping through a selected range efficiently ," later in this chapter.

■ Excel allows multiple selections. For example, you can select a range, press Ctrl, and select another range. You can test for this in your macro and take appropriate action. See "Determining the type of selected range ," later in this chapter.

Note

Using the CurrentRegion property is equivalent to choosing the Home

Editing Find & Select

Go To Special command and selecting the Current Region option (or by using the Ctrl+Shift+* shortcut). To see how this works, record your actions while you issue that command. Generally, the CurrentRegion property setting consists of a rectangular block of cells surrounded by one or more blank rows or columns.

0 0

Post a comment