Useful Range Object Methods in Excel 2016 VBA Programming

By John Walkenbach

As you know, a VBA method performs an action in Excel 2016. A Range object has dozens of methods but, you won’t need most of these. Here, you find some of the most commonly used Range object methods.

The Select method

Use the Select method to select a range of cells. The following statement selects a range in 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 correct 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 displays 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 into 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. That’s something that you can find out by checking with the Help system.

The Clear method

The Clear method deletes the contents of a range, plus all the cell formatting. 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 Excel’s Home  →  Cells   →  Delete   →  Delete Cells 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, a built-in constant is used (xlToLeft) for the argument. You could also use xlUp, another named constant.