Excel Optimize

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. I found that by consulting the VBA Help system. This example also demonstrates that the macro recorder doesn’t always generate the most efficient code.