Excel Optimize

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?

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, that cell is A1.

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 Home➪Editing➪ Find & Select➪Goto Special (which displays the Go To Special dialog box), and choosing 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