Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

The starting point for our example.

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

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:

Enter the cell references in a set of double quotes.

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

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:

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:

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:

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

Run the subroutine to check that it works:

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:

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

Begin a new subroutine and refer to the Range property:

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:

Enter the cell references of the first block of cells.

Now type a comma followed by the next cell reference:

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:

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:

You can modify some formatting properties of the selected cells.

Run the subroutine and check the results:

You should see these cells highlighted.

Selecting a Block of Cells

To practise selecting a block of cells:

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

Sub Format_Gross_Values()

End Sub

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

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

Run the subroutine and check the results:

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

Selecting Non-Adjacent Cells

To practise selecting non-adjacent cells:

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

Sub Format_Star_Wars()

End Sub

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

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

Run the subroutine and check the results:

You should see rows 4 and 10 formatted as shown.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Selecting a Block of Cells

Selecting Non-Adjacent Cells

Selecting a Block of Cells

Selecting Non-Adjacent Cells

Head office

London

Manchester