Useful Range Object Methods in Excel 2016 VBA Programming
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:
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:
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.
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:
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:
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:
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:
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.