Excel 2010 Bible

Chapter 44: VBA Examples

891

Sub MakeRowBold()

ActiveCell.EntireRow.Font.Bold = True

End Sub

Moving a range

Moving a range consists of cutting it to the Clipboard and then pasting it to another area. If you

record your actions while performing a move operation, the macro recorder generates code as

follows:

Sub MoveRange()

Range(“A1:C6”).Select

Selection.Cut

Range(“A10”).Select

ActiveSheet.Paste

End Sub

As demonstrated with copying earlier in this chapter (see “Copying a range”), this method is not

the most efficient way to move a range of cells. In fact, you can do it with a single VBA statement,

as follows:

Sub MoveRange2()

Range(“A1:C6”).Cut Range(“A10”)

End Sub

This statement takes advantage of the fact that the Cut method can use an argument that specifies

the destination.

On the CD
A workbook that contains this macro is available on the companion CD-ROM. The file is named
range move.xlsm.

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they may perform selective actions

based on the content of each cell. These operations usually involve a For-Next loop that pro-

cesses each cell in the range.

The following example demonstrates how to loop through all the cells in a range. In this case, the

range is the current selection. In this example, Cell is a variable name that refers to the cell being

processed. (Notice that this variable is declared as a Range object.) Within the For-Next loop,

the single statement evaluates the cell. If the cell is negative, it’s converted to a positive value.