Excel 2010 Bible

Part VI: Programming Excel with VBA

888

l (^) The macro recorder doesn’t always generate the most efficient code. Often, you can use
the recorder to create your macro and then edit the code to make it more efficient.
l (^) I recommend that you use named ranges in your VBA code. For example, a reference such
as Range (“Total”) is better than Range (“D45”). In the latter case, you need to
modify the macro if you add a row above row 45.
l When you record macros that select ranges, pay close attention to relative versus absolute
recording mode (see Chapter 39). The recording mode that you choose can drastically
affect the way the macro operates.
l (^) If you create a macro that loops through each cell in the current range selection, be aware
that the user can select entire columns or rows. In such a case, you need to create a subset
of the selection that consists only of nonblank cells. Or, you can work with cells in the
worksheet’s used range (by using the UsedRange property).
l (^) Be aware that Excel allows you to select multiple ranges in a worksheet. For example, you
can select a range, press Ctrl, and then select another range. You can test for this in your
macro and take appropriate actions.
The examples in the following sections demonstrate these points.

Copying a range

Copying a range is a frequent activity in macros. When you turn on the macro recorder (using

absolute recording mode) and copy a range from A1:A5 to B1:B5, you get a VBA macro like this:

Sub CopyRange()

Range(“A1:A5”).Select

Selection.Copy

Range(“B1”).Select

ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

This macro works, but it’s not the most efficient way to copy a range. You can accomplish exactly

the same result with the following one-line macro:

Sub CopyRange2()

Range(“A1:A5”).Copy Range(“B1”)

End Sub

This code takes advantage of the fact that the Copy method can use an argument that specifies the

destination. Useful information about properties and methods is available in the Help system.