Module 2 - Moving Around in Excel
Lesson 2.1 - Basic Cell Selection
Topic 2.1.2 - Selecting Multiple Cells

When you want to apply the same changes to many cells, it would be incredibly tedious to have to do it one cell at a time.  Fortunately, you can select and modify multiple cells, as this page shows.

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 finished 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 table we'll work with.

 

Selecting a Block of Cells

For the first example, we'd like to format the column headings of the table.  Start by opening the VBE, inserting a new module and creating a subroutine as shown below:

Basic sub

The starting point for our example.

 

Next, refer to the Range property and open a set of parentheses:

Tooltip

The tooltip seems to indicate that we can only reference two cells, although this is a little misleading.

 

You can enter the address of the first and last cell in the range you want to reference, separated by a colon:

Address

Enter the cell references in a set of double quotes.

 

You can then close the parentheses and apply the Select method to the range:

Select method

Apply the Select method to the range.

 

After selecting a single cell you can use the ActiveCell property to reference it.  When you have multiple cells selected however, this isn't much use:

Activecell

The ActiveCell property only returns a reference to a single cell, not the entire range that is selected. Here the active cell is A1.

 

To refer to the entire range of selected cells, you can use the Selection property:

Selection

The Selection property returns a reference to the entire range of selected cells.

 

You can apply methods and properties to the range returned by Selection, although you sadly won't receive any help from the IntelliSense list:

Properties

Here we're changing the fill colour of the selected cells.

 

Run the subroutine to check that it works:

Formatted

You should see something resembling this.

 

Selecting Non-Adjacent Cells

You can select multiple cells which aren't next to each other in a worksheet.  For the next example we'd like to highlight the details of the two Avengers films in the list:

Avengers

We want to select cells A6:D6 and A8:D8 at the same time.

 

Begin a new subroutine and refer to the Range property:

Range

We can pass as many cell references as we like into the Cell1 parameter.

 

Open a set of double-quotes and write the address of the first block of cells:

First reference

Enter the cell references of the first block of cells.

 

Now type a comma followed by the next cell reference:

Next cell ref

Enter the address of the second block of cells.

 

You can continue listing cell references in this way, separated by commas.  When you've finished listing cells, close the double-quotes and parentheses:

Finish reference

You can apply the Select method to the list of ranges that you've referenced.

 

You can use the Selection property again to refer to all of the selected cells:

Selection

You can modify some formatting properties of the selected cells.

 

Run the subroutine and check the results:

Results

You should see these cells highlighted.

 

Selecting a Block of Cells

To practise selecting a block of cells:

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

Sub Format_Gross_Values()

 

End Sub

  1. Add a line to select the range of cells containing numbers in the World gross column:

Sub Format_Gross_Values()

 

Range("C2:C11").Select

 

End Sub

  1. Add an instruction to apply a number format to the selected cells:

Sub Format_Gross_Values()

 

Range("C2:C11").Select

 

Selection.NumberFormat = "\$#,##0"

 

End Sub

  1. Run the subroutine and check the results:
Formatted numbers

The numbers in column C should be formatted as shown here.

 

Selecting Non-Adjacent Cells

To practise selecting non-adjacent cells:

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

Sub Format_Star_Wars()

 

End Sub

  1. Add an instruction to select the details of the two Star Wars films in the list:

Sub Format_Star_Wars()

 

Range("A4:D4, A10:D10").Select

 

End Sub

  1. Add two instructions to change the fill and font colours of the selected cells:

Sub Format_Star_Wars()

 

Range("A4:D4, A10:D10").Select

 

Selection.Interior.Color = rgbBlack

Selection.Font.Color = rgbYellow

 

End Sub

  1. Run the subroutine and check the results:
Formatted cells

You should see rows 4 and 10 formatted as shown.

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