Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

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 47.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 47.1

This range can consist of any number of rows.

The macro that follows demonstrates how to copy this range 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

Another approach is to use a table to store the data. When you add new rows to a table, the

table’s range address adjusts automatically, so you can use a procedure like this:

Sub CopyTable()

Range("Table1[#All]").Copy Sheets("Sheet2").Range("A1")

End Sub