Excel VBA Select and Activate Methods

Basics of Coding Select and Activate Methods Create your own Excel Functions

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

This is it. 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
Sheet(3).Select                                 The argument 3 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