Module 2 - Moving Around in Excel
Lesson 2.1 - Basic Cell Selection
Topic 2.1.1 - Selecting Single Cells

You can't do much in Excel VBA without knowing how to refer to and select cells!  In this part of the lesson, you'll learn a few of the basic techniques for selecting single cells.

Files Needed

You can click here to download the file used for this page.

Completed Code

You can click here to download a file containing the sample code.

To get started, extract and open the file linked to in the Files Needed section above.  You should see a basic table of information about films:

Basic table

This is the basic data we'll work with.

 

Now open the VBE and insert a new module into the project.

Selecting a Cell by Reference

You can select a cell by its reference using the Range property.  To demonstrate this, create a new subroutine and begin writing an instruction as shown below:

Range property

The tooltip indicates that you need to enter a reference to at least one cell.

 

In this example, we'll refer to cell which contains the name of the best film in the list:

Ragne B6

Enter the cell reference within a set of double-quotes. Feel free to disagree with the choice of best film.

 

We can now apply a method to select the cell we've referenced:

Select

Choose the Select method from the IntelliSense list or simply type it in.

 

Once a cell has been selected we can refer to it with the ActiveCell property.  We can use this to highlight the selected cell in a different colour:

Change colour

You can change the cell colour as shown. Type rgb and then press Ctrl + Space to show the list of colours.

 

You can run the subroutine to check that the correct cell is selected and coloured:

End result

The end result should resemble the example shown here. Feel free to choose a different cell and fill colour!

 

You can also refer to a cell by writing its reference in a set of square brackets.  This is an old-fashioned technique but is quicker to write.  The example below would select cell B6:

[B6].Select

Select a Cell by Row and Column Number

If you prefer to, you can select a cell using its row and column number by using the Cells property.  Create a new subroutine and begin writing an instruction as shown below:

Cells property

The tooltip indicates that you can enter a row and column number to reference a cell.

 

Next, enter the number of the row followed by the number of the column.  In the example below we're referring to cell B3:

Row and column

Enter the two numbers, separated by a comma.

 

You can then apply the Select method to the cell:

Select cell

You won't see the IntelliSense list appear when referring to a single cell with the Cells property.

 

Once the cell has been selected, you can use the ActiveCell property to refer to it and change some of its properties:

Change properties

Here we're changing the fill colour of the cell.

 

When you run the subroutine you'll see the specified cell's fill colour has changed:

Coloured cell

This time we've highlighted the worst film in the list.

 

Using Cell References

To practise selecting single cells using cell references:

  1. Using the same module, create a new subroutine as shown below:

Sub Copy_Best_Film()

 

End Sub

  1. Add two instructions to select and then write a label into cell A14 (use the Value property to change the text in a cell):

Sub Copy_Best_Film()

 

Range("A14").Select

ActiveCell.Value = "Best"

 

End Sub

  1. Add two instructions to select and then copy the cell containing the name of your favourite film in the list:

Sub Copy_Best_Film()

 

Range("A14").Select

ActiveCell.Value = "Best"

 

Range("B6").Select

ActiveCell.Copy

 

End Sub

  1. Add two more instructions to select and paste the copied cell into cell B14:

Sub Copy_Best_Film()

 

Range("A14").Select

ActiveCell.Value = "Best"

 

Range("B6").Select

ActiveCell.Copy

 

Range("B14").Select

ActiveCell.PasteSpecial

 

End Sub

  1. Run the subroutine and check the results:
Copied cell

The selected cell, along with all of its formatting, will be copied to B14.

 

Using Row and Column Numbers

To practise selecting cells using row and column numbers:

  1. Using the same module, create a new subroutine as shown below:

Sub Copy_Worst_Film()

 

End Sub

  1. Add instructions to select and write a label into cell A15 using row and column numbers:

Sub Copy_Worst_Film()

 

Cells(15, 1).Select

ActiveCell.Value = "Worst"

 

End Sub

  1. Add two instructions to select and copy the cell containing the name of your least favourite film:

Sub Copy_Worst_Film()

 

Cells(15, 1).Select

ActiveCell.Value = "Worst"

 

Cells(3, 2).Select

ActiveCell.Copy

 

End Sub

  1. Add two instructions to select and paste the copied cell into cell B15:

Sub Copy_Worst_Film()

 

Cells(15, 1).Select

ActiveCell.Value = "Worst"

 

Cells(3, 2).Select

ActiveCell.Copy

 

Cells(15, 2).Select

ActiveCell.PasteSpecial

 

End Sub

  1. Run the subroutine and check the results:
Copied cell

The selected cell will be copied, along with its formatting, into the specified cell.

 
  1. Save and close the workbook.
This page has 0 threads Add post