VBA Course: Selections

We are going to create a macro that will select a cell of our choice.

Open the editor and add a module to it:

In the module, type sub example and press Enter.

You will notice that Excel has automatically added the end of this new procedure:

Sub example()

End Sub

Now create a form button which you are going to associate with this macro (currently empty):

Complete your macro with this:

Sub example()

    'Selection of cell A8
    Range("A8").Select

End Sub

You can test this macro by clicking on your form button, cell A8 is then selected.

We are now going to modify this macro to select cell A8 from the second sheet:

Sub example()

    'Activation of sheet 2
    Sheets("Sheet2").Activate

    'Selection of cell A8
    Range("A8").Select

End Sub

Excel then activates sheet 2 before selecting cell A8.

Selection of a range of cells

Sub example()

    'Selection of cells A1 to A8
    Range("A1:A8").Select

End Sub

Selection of distinct cells

Sub example()

    'Selection of cells A8 and C5
    Range("A8, C5").Select

End Sub

Selection of a named cell range

Sub example()

    'Selection of the cells in the range "my_range"
    Range("my_range").Select

End Sub

Selection of a cell based on a row and column number

Sub example()

    'Selection of the cell from row 8 and column 1
    Cells(8, 1).Select

End Sub

This other way of selecting allows for more dynamic selections and will be very useful later on.

Here is an example:

Sub example()

    'Random selection of a cell from row 1 to 10 and column 1
    Cells(Int(Rnd * 10) + 1, 1).Select

    'Translation:
    'Cells([random_number_between_1_and_10], 1).Select

End Sub

Here, the row number is Int(Rnd * 10) + 1, in other words a number between 1 and 10 (no need to remember this code for the moment).

Selection of rows

It is possible to select entire rows with Range or Rows (Rows being specific to rows):

Sub example()

    'Selection of rows 2 to 6
    Range("2:6").Select

End Sub

Sub example()

    'Selection of rows 2 to 6
    Rows("2:6").Select

End Sub

Selection of columns

Just like for rows, it is possible to select entire columns with Range or Columns (Columns being specific to columns):

Sub example()

    'Selection of columns B to G
    Range("B:G").Select

End Sub

Sub example()

    'Selection of columns B to G
    Columns("B:G").Select

End Sub