Working with Ranges

Most of your VBA programming probably involves worksheet ranges. (For a refresher course on Range objects, refer to Chapter 8.) When you work with Range objects, keep the following points in mind:

11 Your VBA doesn't need to select a range to work with it. 1 If your code does select a range, its worksheet must be active.

1 The macro recorder doesn't always generate the most efficient code. Often, you can create your macro by using the recorder and then edit the code to make it more efficient.

1 It's a good idea to use named ranges in your VBA code. For example, using Range("Total") is better than using Range("45"). In the latter case, if you add a row above row 45, you need to modify the macro so that it uses the correct range address (D46). Note that you name a range of cells by choosing FormulasODefined Names OName A Range.

1 When running a macro that works on the current range selection, the user might select entire columns or rows. In most cases, you don't want to loop through every cell in the selection (that could take a long time). Your macro should create a subset of the selection consisting of only the nonblank cells.

1 Excel allows multiple selections. For example, you can select a range, press Ctrl, and select another range. (Do your range selection with the mouse, of course.) Your code can test for a multiple selection and take appropriate actions.

The examples in this section, which are available at this book's Web site, demonstrate these points.

If you prefer to enter these examples yourself, press Alt+F11 to activate the VBE. Then insert a VBA module and type the code. Make sure that the workbook is set up properly. For instance, if the example uses two sheets named Sheetl and Sheet2, make sure that the workbook has sheets with those names.

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:







Application.CutCopyMode =

= False



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:




1.Copy Range("B1")



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.

Copying a Variable-sized range

In many cases, you need to copy a range of cells but don't know the exact row and column dimensions. For example, you might have a workbook that tracks weekly sales. The number of rows changes as you add new data.

Figure 14-1 shows a range on a worksheet. This range consists of several rows, and the number of rows can change from day to day. Because you don't know the exact range address at any given time, writing a macro to copy the range can be challenging. Are you up for the challenge?

Figure 14-1:

This range can consist of any number of rows.

^H examples, xlsm

. n X

Il A



G H 1





Units Amount



132 £2.737



143 $154



133 $109



169 $614



102 $2,744



143 $5,164



109 $4,314



122 $4,448



156 $4,657




187 $6,989



140 $2,014



132 $1,070










► h I Sheetl , Sheet2 daily .

The following macro demonstrates how to copy this range from Sheet1 to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of cells around a particular cell. In this case, that cell is A1.









Application.CutCopyMode =




Using the CurrentRegion property is equivalent to choosing HomeOEditingO Find & SelectOGoto Special (which displays the Go To Special dialog box), and choosing the Current Region option. To see how this works, record your actions while issuing that command. Generally, the CurrentRegion consists of a rectangular block of cells surrounded by one or more blank rows or columns.

You can make this macro even more efficient by not selecting the destination. The following macro takes advantage of the fact that the Copy method can use an argument for the destination range:

Sub CopyCurrentRegion2()

Range("A1").CurrentRegion.Copy _

Sheets("Sheet2").Range("A1") Application.CutCopyMode = False End Sub

Selecting to the end of a row or column

You're probably in the habit of using key combinations such as Ctrl+Shift+Right Arrow and Ctrl+Shift+Down Arrow to select a range that consists of everything from the active cell to the end of a row or a column. Not surprisingly, you can write macros that perform these types of selections.

You can use the CurrentRegion property to select an entire block of cells. But what if you want to select, say, one column from a block of cells? Fortunately, VBA can accommodate this type of action. The following VBA procedure selects the range beginning at the active cell and extending down to the cell just above the first blank cell in the column. After selecting the range, you can do whatever you want with it — copy it, move it, format it, and so on.

Sub SelectDown()

Range(ActiveCell, ActiveCell.End(xlDown)).Select End Sub

This example uses the End method of the ActiveCell object, which returns a Range object. The End method takes one argument, which can be any of the following constants:

1 xlUp 1 xlDown 1 xlToLeft 1 xlToRight

Keep in mind that it's unnecessary to select a range before doing something with it. The following macro applies bold formatting to a variable-sized (single column) range without selecting the range:

Sub MakeBold()

Range(ActiveCell, ActiveCell.End(xlDown)) _ .Font.Bold = True

End Sub

Selecting a row or column

The following procedure demonstrates how to select the column that contains the active cell. It uses the EntireColumn property, which returns a Range object that consists of a full column:

Sub SelectColumn()

ActiveCell.EntireColumn.Select End Sub

As you may expect, VBA also offers an EntireRow property, which returns a Range object that consists of an entire row.

Moving a range

You move a range by cutting it to the Clipboard and then pasting it in another area. If you record your actions while performing a move operation, the macro recorder generates code like the following:









As with the copying example earlier in this chapter, this is not the most efficient way to move a range of cells. In fact, you can move a range with a single VBA statement, as follows:




i .Cut Range("A10")



This macro takes advantage of the fact that the Cut method can use an argument that specifies the destination. Notice also that the range was not selected. The cell pointer remains in its original position.

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they might perform selected actions based on each cell's content. These macros usually include a For-Next loop that processes each cell in the range.

The following example demonstrates how to loop through a range of cells. In this case, the range is the current selection. A variable named Cell refers to the cell being processed. Within the For-Next loop, the single statement evaluates the cell and changes its interior color if the cell contains a positive value.



Dim Cell As Range

For Each Cell In Selection

If Cell.Value > 0 Then Cell.Interior.Color = vbRed

Next Cell



This example works, but what if the selection consists of an entire column or row? This is not uncommon because Excel lets you perform operations on entire columns or rows. In such a case, the macro seems to take forever because it loops through each cell in the selection — even the blank cells. To make the macro more efficient, you need a means for processing only the nonblank cells.

The following routine does just that by using the SpecialCells method. (Refer to the VBA Help system for specific details about its arguments.) This routine uses the Set keyword to create two new Range objects: the selection's subset that consists of cells with constants and the selection's subset that consists of cells with formulas. The routine processes each of these subsets, with the net effect of skipping all blank cells. Pretty slick, eh?

Sub SkipBlanks()

Dim ConstantCells As Range Dim FormulaCells As Range Dim cell As Range ' Ignore errors

On Error Resume Next ' Process the constants

Set ConstantCells = Selection _

.SpecialCells(xlConstants) For Each cell In ConstantCells If cell.Value > 0 Then cell.Interior.Color = vbRed End If Next cell ' Process the formulas

Set FormulaCells = Selection _

.SpecialCells(xlFormulas) For Each cell In FormulaCells If cell.Value > 0 Then cell.Interior.Color = vbRed End If Next cell End Sub

The SkipBlanks procedure works equally fast, regardless of what you select. For example, you can select the range, all columns in the range, all rows in the range, or even the entire worksheet. It's a vast improvement over the ProcessCells procedure presented earlier in this section.

Notice that I use the following statement in this code:

On Error Resume Next

This statement tells Excel to ignore any errors that occur and simply process the next statement (see Chapter 12 for a discussion of error handling). This statement is necessary because the SpecialCells method produces an error if no cells qualify.

Using the SpecialCells method is equivalent to choosing the HomeOEditingO Find & SelectOGoto Special command, and selecting the Constants option or the Formulas option. To get a feel for how this works, record your actions while you issue that command and select various options.

Prompting for a cell Value

As shown in Figure 14-2, you can use VBA's InputBox function to get a value from the user. Then you can insert that value into a cell. The following procedure demonstrates how to ask the user for a value and place the value in cell A1 of the active worksheet, using only one statement:




).Value =

InputBox( _


the value

for cell A1")



Use the VBA InputBox function to get a value from the user.

If you try out this example, you find that clicking the Cancel button in the Input Box erases the current value in cell A1. The following macro demonstrates a better approach: using a variable (x) to store the value entered by the user. If the value is not empty (that is, the user didn't click Cancel), the value of x is placed into cell A1. Otherwise, nothing happens.

Sub GetValue2()

Dim x as Variant x = InputBox("Enter the value for cell A1") If x <> "" Then Range("A1").Value = x End Sub

The variable x is defined as a variant because it could be a number or an empty string (if the user clicks Cancel).

Determining the selection type

If you design your macro to work with a range selection, the macro must be able to determine whether a range is actually selected. If something other than a range is selected (such as a chart or a shape), the macro will probably bomb. The following procedure uses the VBA TypeName function to identify the type of object that is currently selected:

Sub SelectionType()

MsgBox TypeName(Selection) End Sub

Figure 14-2:

Use the VBA InputBox function to get a value from the user.

If a Range object is selected, the MsgBox displays Range. If your macro works only with ranges, you can use an If statement to ensure that a range is selected. This example displays a message and exits the procedure if the current selection is not a Range object:



If TypeName(Selection) <> "Range" Then

MsgBox "Select a range."

Exit Sub

End If

... [Other statements go here]



Identifying a multiple selection

As you know, Excel allows multiple selections by pressing Ctrl while choosing objects or ranges. This can cause problems with some macros. For example, you can't copy a multiple selection that consists of nonadjacent cells. (Try it if you don't believe me.)

The following macro demonstrates how to determine whether the user made a multiple selection so that your macro can take appropriate action:



If Selection.Areas.Count > 1 Then

MsgBox "Multiple selections not allowed."

Exit Sub

End If

... [Other statements go here]



This example uses the Areas method, which returns a collection of all objects in the selection. The Count property returns the number of objects in the collection.

0 0

Post a comment