Microsoft Excel Training Videos


Selecting and activating cells using a macro in Microsoft Excel


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 b6 using Visual Basic, 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. The training video shows the Excel macro in action.
Sub EnterFormula()
Worksheets("Sheet1").Range("b6").Formula = "=SUM(b2:b5)"
End Sub



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.
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



Custom Search

Microsoft Excel Training Videos