VBA Programming Series...34

Some Useful Range Object Methods

As you know, a VBA method performs an action. A Range object has dozens of methods but, again, you won’t need most of these. In this section, I point out some of the more commonly used Range object methods.

The Select method

Use the Select method to select a range of cells. The following statement selects a range on the active worksheet:

Range(“A1:C12”).Select

Before selecting a range, it’s often a good idea to use one additional statement to ensure that the correct worksheet is active. For example, if Sheet1 contains the range you want to select, use the following statements to select the range:

Sheets(“Sheet1”).Activate

Range(“A1:C12”).Select

Contrary to what you may expect, the following statement generates an error if Sheet1 is not already the active sheet. In other words, you must use two statements rather than just one: one to activate the sheet and another to select the range.

Sheets(“Sheet1”).Range(“A1:C12”).Select

If you use the GoTo method of the Application object to select a range, you can forget about selecting the right worksheet first. This statement activates Sheet1 and then selects the range:

Application.Goto Sheets(“Sheet1”).Range(“A1:C12”)

The GoTo method is the VBA equivalent of pressing F5 in Excel, which dis-plays the GoTo dialog box.

The Copy and Paste methods

You can perform copy and paste operations in VBA by using the Copy and Paste methods. Note that two different objects come into play. The Copy method is applicable to the Range object, but the Paste method applies to the Worksheet object. It actually makes sense: You copy a range and paste it to a worksheet.

This short macro (courtesy of the macro recorder) copies range A1:A12 and pastes it to the same worksheet, beginning at cell C1:

Sub CopyRange()

Range(“A1:A12”).Select

Selection.Copy

Range(“C1”).Select

ActiveSheet.Paste

End Sub

Notice that in the preceding example, the ActiveSheet object is used with the Paste method. This is a special version of the Worksheet object that refers to the currently active worksheet. Also notice that the macro selects the range before copying it. However, you don’t have to select a range before doing something with it. In fact, the following procedure accomplishes the same task as the preceding example by using a single statement:

Sub CopyRange2()

Range(“A1:A12”).Copy Range(“C1”)

End Sub

This procedure takes advantage of the fact that the Copy method can use an argument that corresponds to the destination range for the copy operation.

The Clear method

The Clear method deletes the contents of a range, plus all of the cell format-ting. For example, if you want to zap everything in column D, the following statement does the trick:

 

Columns(“D:D”).Clear

You should be aware of two related methods. The ClearContents method deletes the contents of the range but leaves the formatting intact. The ClearFormats method deletes the formatting in the range but not the cell contents.

The Delete method

Clearing a range differs from deleting a range. When you delete a range, Excel shifts the remaining cells around to fill up the range you deleted.

The following example uses the Delete method to delete row 6:

Rows(“6:6”).Delete

When you delete a range that’s not a complete row or column, Excel needs to know how to shift the cells. (To see how this works, experiment with the Excel Home➪Cells➪Delete command.)

The following statement deletes a range and then fills the resulting gap by shifting the other cells to the left:

Range(“C6:C10”).Delete xlToLeft

The Delete method uses an argument that indicates how Excel should shift the remaining cells. In this case, I use a built-in constant (xlToLeft) for the argument. I could also use xlUp, another named constant.