Using the Select and Activate Methods


Before you begin formatting, entering, or retrieving values from cells you need to know the different ways of selecting cells.   The Select method is used to make the selected cell(s) active.   The Select method allows you to accomplish the same thing as when you use your mouse to select cells.  You will probably use the Select method more than any other method so you need to become very familiar with it. 

Two of the various methods for selecting individual cells are the Range method and the Cells methods.  The Cells method uses the RowIndex and ColumnIndex for its arguments while the Range method uses Cell Address for its arguments.

The Syntax for the Cells function is:

      Cells(RowIndex, ColumnIndex)

The Syntax for the Range function is:

      Range(Cell1, [Cell2]…)

The following two lines of code both select cell B7. 

Range(“B7”).Select
Cells(7, 2).Select
          Selects the cell in Row 7 column 2   (which is cell B7)

Select Individual Cells

Range("C5").Select                                    Selects cell C5 on the current worksheet
Range("E3").Select                                    Selects cell E3 on the current worksheet
ActiveSheet.Cells(3, 5).Select       Selects cell E3 on the current worksheet
Cells(3, 5).Select                                    Selects cell E3 on the current worksheet

Select Noncontiguous Cells


Range("B3, E3, C6, D7").Select              Select individual cells, the order doesn’t matter except that
                                                                                      the last cell entered will be the active cell.

ActiveSheet.Range("C5, D10, B6").Select       Selects the three cells on the current worksheet

Select a Row

Rows("2").Select         (Select all of row 2)
Rows("3:8").Select    (Select rows 3 through 8)

Select a Column


Columns("D").Select     (Select All of Column D)
Columns("D:F").Select   (Select Columns D through F)

Select a Range on the Current Sheet


ActiveSheet.Range("B3:E6").Select       (Selects All cells in the Range of B3:E6)

The following statement will select all the cells in the range named MonthlySales on the current worksheet

Range("MonthlySales").Select

Select All Cells on the Current Worksheet


ActiveSheet.Cells.Select 
 or     Cells.Select
 

Select a different Worksheet

Before you select a range on another worksheet you of course need to first select the worksheet.

Sheets("Sheet2").Select      Selects the Sheet2 tab which makes Sheet2 the active sheet Sheet3.Select                                       Selects Sheet3
Sheet(3).Select
                                  The argument is the index number. Selects the third worksheet                                                                       in the workbook.

Selecting Cells on another Worksheet

To select a named range on another worksheet, first select the worksheet

Sheet3.Select
Range("MonthlySales").Select

Sheets(2).Select 
Range("B3").Select

Select cells on other Workbooks

A reference to a cell in another workbook is called a link.

The Application.Goto method is used for selecting ranges in other workbooks .  It combines the workbook, sheet, and range selection in one statement using either one of the following formats: 

Application.Goto Workbooks("Book1.xlsx").Sheets("Sheet1").Range("B3")

Application.Goto Workbooks("Book1.xlsx").Sheets("Sheet1").Cells(3, 2)

Activating a cell within a selected range

Range("A1:F5").Select
Range("D3").Activate

Activate a cell in a selection