Selecting and Activating Cells

  • Article

When you work with Microsoft Excel, you usually select a cell or cells and then perform an action, such as formatting the cells or entering values in them. In Visual Basic, it is usually not necessary to select cells before modifying them.

For example, if you want to enter a formula in cell D6 using Visual Basic, you do not need to select the range D6. You just need to return the Range object and then set the Formula property to the formula you want, as shown in the following example.

  Sub EnterFormula()
    Worksheets("Sheet1").Range("D6").Formula = "=SUM(D2:D5)"
End Sub

For examples of using other methods to control cells without selecting them, see How to: Reference Cells and Ranges.

Using the Select Method and the Selection Property

The Select method activates sheets and objects on sheets; the Selection property returns an object that represents the current selection on the active sheet in the active workbook. Before you can use the Selection property successfully, you must activate a workbook, activate or select a sheet, and then select a range (or other object) using the Select method.

The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it illustrates how Select and Selection work together.

  Sub Macro1()
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Address"
    Range("A1:B1").Select
    Selection.Font.Bold = True
End Sub

The following example accomplishes the same task without activating or selecting the worksheet or cells.

  Sub Labels()
    With Worksheets("Sheet1")
        .Range("A1") = "Name"
        .Range("B1") = "Address"
        .Range("A1:B1").Font.Bold = True
    End With
End Sub

Selecting Cells on the Active Worksheet

If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells. For example, the following procedure copies a row from Sheet1 to Sheet2 in the active workbook.

  Sub CopyRow()
    Worksheets("Sheet1").Rows(1).Copy
    Worksheets("Sheet2").Select
    Worksheets("Sheet2").Rows(1).Select
    Worksheets("Sheet2").Paste
End Sub

Activating a Cell Within a Selection

You can use the Activate method to activate a cell within a selection. There can be only one active cell, even when a range of cells is selected. The following procedure selects a range and then activates a cell within the range without changing the selection.

  Sub MakeActive()
    Worksheets("Sheet1").Activate
    Range("A1:D4").Select
    Range("B2").Activate
End Sub

See Also