Copying a range

Copying a range ranks right up there as one of the most favorite Excel activities of all time. When you turn on the macro recorder and copy a range from A1:A5 to B1:B5, you get this VBA macro:

Sub

CopyRange()

Range("A1:A5").Select

Selection.Copy

Range("B1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

End

Sub

Notice the last statement. This statement was generated by pressing Esc, which cancels the marching ants display that appears in the worksheet when you copy a range.

This macro works fine, but you can copy a range more efficiently than this. You can produce the same result with the following one-line macro, which doesn't select any cells:

Sub CopyRange2()

Range("A1:A5").Copy Range("B1") End Sub

This procedure takes advantage of the fact that the Copy method can use an argument that specifies the destination. This example also demonstrates that the macro recorder doesn't always generate the most efficient code.

0 0

Post a comment