Select Activate Excel Cells VBA

Selecting and activating cells using a macro in Microsoft Excel
When you use a macro in Microsoft Excel, it is usually not necessary to select cells before modifying them. We describe here methods of selecting and activating cells and performing calculations using a macro.
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 b6 using Visual Basic for Applications, you don’t need to select the range b6. 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.
Macro code:
Sub EnterFormula()
Worksheets(“Sheet1”).Range(“b6”).Formula = “=SUM(b2:b5)”
End Sub

The MS Excel training video shows the macro in action.

Using the Select Method and the Selection Property in Excel
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.
Try this out!
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 in Excel.
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 in Excel
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.
Try this out!
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 in Excel
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.
Try this out!
Sub MakeActive()
Worksheets(“Sheet1”).Activate
Range(“A1:D4”).Select
Range(“B2”).Activate
End Sub

Further reading:

Selecting and Activating Cells [Excel 2003 VBA Language Reference]

Leave a Reply

Your email address will not be published. Required fields are marked *