Some Useful Range Object Methods

SOME USEFUL RANGE OBJECT METHODS

A VBA method performs an action. A Range object has dozens of methods, some of which will be covered here.

The Select property

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

 
Range("A1:C12").Select

Before selecting a range, make sure you've activated the range's worksheet, otherwise you get an error or the wrong range is selected.

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

The following statement generates an error. You must use two statement instead of just one: one to activate the sheet and another on to select the range:

    Sheets("Sheet1").Range("A1:C12").Select

The Copy and Paste methods

You can perform copy and paste operations in VBA by using the Copy and Paste methods. The copy method is applicable to the Range object, but the Past method is applicable to the Worksheet object.

For example, this macro copies the range A1:A12 and paste it to the range beginning at cell C1:

    Sub CopyRange()
        Range("A1:A12").Select
        Selection.Copy
        Range("C1").Select
        ActiveSheet.Paste
    End Sub

Actually you don't have to select a range before doing something to it. In fact the following procedure accomplishes the same task as the preceding example

    Sub CopyRange2()
        Range("A1:A12").Copy Range("C1")

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 and all the cell formatting.

For example, if you want to zap everything in column D, use the following statement:

    Columns("D:D").Clear

There are 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 uses the Delete method to delete row 6:

    Rows("6:6").Delete

When you delete a range that is not a complete row or column, Excel needs to know how to shift the cells.

For example, the following statement delete 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 to indicate how Excel should shift the remaining cells. Here the built-in constant xlToLeft is used. Another constant is xlUp (for shifting up).