Selecting and Activating Cells [Excel 2003 VBA Language Reference]
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 don't 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