Excel 2019 Bible

Chapter 47: Seeing Some VBA Examples

47

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.

A workbook that contains this macro is available on this book’s website at http://www.wiley.com/go/

excel2019bible. 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 processes each cell in the range.

The following example demonstrates how to loop through all of 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:

Sub ProcessCells()

Dim Cell As Range

For Each Cell In Selection.Cells

If Cell.Value < 0 Then Cell.Value = Cell.Value * -1

Next Cell

End Sub

The preceding example works, but what if the selection consists of an entire column or an
entire range? This is not uncommon because Excel lets you perform operations on entire
columns or rows. In this case, though, the macro seems to take forever because it loops