Sheets and Cells

Selections:

We'll begin by creating a macro that selects the cell that we specify.

First open the editor and add a module : 

 In the module, type "Sub Select_Cell " and press Enter.

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

Sub Select_Cell ()

End Sub

Now create a formula button to which you will associate this macro (it is empty for now) :

Complete your macro with this code :

Sub Select_Cell()

   'Select cell B5

   Range("B5").Select

End Sub

You can test this macro by clicking on your formula button, and you will see that cell B5 is now selected.

We will now edit the macro so that it selects cell B5 on the second worksheet :

Sub Select_Cell ()

   'Activating of Sheet 2

   Sheets("Sheet2").Activate

   'Selecting of Cell B5

   Range("B5").Select

End Sub

Excel will now activate Sheet 2 and then select cell B5.

Note : the comments (text in green) will help you understand the macros in this course correctly. 

Selecting different cells:

 Sub Select_Cell ()

   'Selecting A2 and D10

   Range("A2, D10").Select

End Sub

Selecting a range of cells:

 Sub Select_Cell ()

   'Selecting cells A1 to B10

   Range("A1:B10").Select

End Sub

Selecting a range of cells that has been renamed:

Sub Select_Cell ()

   'Selecting cells from the "Test_Range" range

   Range("Test_Range ").Select

End Sub

Selecting a cell by row and column number:

 Sub Select_Cell ()

   'Selecting the cell in row 5 and column 5

   Cells(5, 5).Select

End Sub

This method of selecting cells allows for more dynamic selections. It will be quite useful further along.

Here is a little example :

Sub Select_Cell ()

   'Random selection of a cell from row 1 to 20 and column 1

   Cells(Int(Rnd * 20) + 1, 1).Select

   'Cells([random_number_between_1_and_20], 1).Select

End Sub

In this case, the row number is : Int(Rnd * 20) + 1, or in other words : a number between 1 and 20 (there's no reason you should learn this code at this point).

Moving a selection:

Sub Select_Cell ()

   'Selecting a cell (described in relation to the cell that is currently active)

   ActiveCell.Offset(5, 4).Select

End Sub

Moving the selection box five rows down and four columns to the right :

Selecting rows:

It is possible to select entire rows using the Range or Rows commands (the Rows command is of course specific to rows).

Sub Select_Cell()

   'Selecting rows 2 to 6

   Range("2:6").Select

End Sub

Selecting columns:

As with rows, it is possible to select entire columns using the Range or Columns commands (the Columns command is of course specific to columns).

Sub Select_Cell ()

   'Selecting columns B to G

   Range("B:G").Select

End Sub