Module 2 - Moving Around in Excel
Lesson 2.2 - Working with Ranges
Topic 2.2.2 - Selecting an Entire List

In the previous part of this lesson you learnt how to find the bottom of a list using the End property.  This part of the lesson expands on this idea and explains how to select an entire list, from top to bottom.

Files Needed

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

Completed Code

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

The Sample Data

Extract and open the file linked to in the Files Needed section above.  You'll find a workbook containing data about the 2017-2018 NFL season:

NFL 2017

We'd like to copy the table for each division into the appropriate worksheet.

Referring to a Fixed Range of Cells

We'll start by looking at how to select a table with cell references, using the AFC East division as an example.  In the VBE, insert a new module and create a subroutine which starts by selecting the NFL 2017 worksheet:

Start of sub

Begin the subroutine as shown here.

 

If we know the range of cells that we want to copy, it's simple enough to use the references of those cells.  In this case, the range of cells is A5 to D9:

Range to copy

We want to copy five rows and four columns.

 

We can reference these cells using the Range property, as we have in previous lessons:

Use Range

This style of cell referencing uses the first parameter of the Range property, called Cell1.

 

After referencing the range of cells we can apply the Copy method to it:

Copy cells

This instruction will copy the fixed range of cells we have referenced.

 

To paste the data into another worksheet we can either use the PasteSpecial method or set the Destination parameter of the Copy method.  In the example below we use the Destination parameter:

Destination

We set the Destination to refer to cell A1 on the AFC East worksheet.

After running the subroutine we can check that the results appear on the correct worksheet:

Results

You'll need to select the AFC East worksheet to see the results.

 

Using the Cell2 Parameter

For the next example we'll copy the NFC East table to the correct worksheet.  Create a new subroutine which starts by selecting the NFL 2017 worksheet:

New sub

The start of the subroutine.

 

This time, the range of cells we want to copy is F5 to I9:

Cells to copy

We want to copy the NFC East table.

Add an instruction which begins referring to cell F5:

Cell F5

We're passing F5 to the Cell1 parameter.

 

Next, enter a comma followed by the second cell reference:

Cell2

We're passing I9 to the Cell2 parameter.

 

You can then close the parentheses and apply the Copy method as before:

Copy

This instruction will copy every cell from F5 to I9.

 

When you want to reference a fixed range of cells, using the Cell2 parameter is perhaps a little awkward compared to using only the Cell1 parameter.  Using the Cell2 parameter is key to the next technique you'll learn however!

You can set the Destination parameter of the Copy method to send the copied cells to the correct worksheet:

Destination

Set the Destination to cell A1 on the NFC East worksheet.

You can run the subroutine and check that the results appear in the correct place:

Results

Select the NFC East worksheet to see the results.

 

Finding the Bottom Corner of a Table

Using cell references works well when you know the size of the table you want to reference.  When the table can vary in size, you can use the End property to find its bottom corner.  Create a new subroutine which starts by selecting the NFL 2017 worksheet:

Start sub

The now-familiar beginning to the subroutine.

 

In this example, we'll copy the AFC North table:

AFC North

For this example we'll assume that we know the table begins in cell A12 but that we don't know how many rows and columns it contains.

 

Add a new instruction which begins by referencing cell A12:

Cell A12

We'll assume that we know where the table starts.

 

Next, we'll use the End property to find the bottom row of the table.  We'll do this by setting the Cell2 parameter of the Range property:

End

We set the Cell2 parameter to reference the cell at the end of the list moving downwards from cell A12.

 

To demonstrate what's happened so far, let's apply the Select method so that we can see which range of cells we're currently referring to:

Select

Make sure that you type two closing parentheses before applying the Select method.

 

Run the subroutine and check the results in Excel:

Result

You should find that cells A12 to A16 are selected.

 

At this point we've selected from cell A12 to the bottom of the list:

Currently

This diagram shows which cells the Cell1 and Cell2 parameters are currently pointing to.

 

We now need to make the Cell2 parameter reference the cell in the bottom right corner of the table:

Extend

The Cell2 parameter should refer to cell D16.

 

We can do this by using a second End property to find the right hand edge of the table.  Start by positioning the cursor between the two closing parentheses as shown below:

Click

Click between the two closing parentheses.

 

Now apply another End property to the range returned by the first End property:

Second End

This time use the xlToRight direction.

Run the subroutine to check that the correct block of cells is selected:

Selected cells

You should find this block of cells is selected.

 

Now you can replace the Select method with the Copy method to copy the cells to the correct location:

Copy cells

The final subroutine should look like this.

Run the subroutine and check that the cells are copied to the correct sheet:

Copied cells

The cells should appear on the AFC North worksheet.

 

A Quick Way to Select a Table

If you want to reference an entire table, you don't need to use the End property to do so.  To demonstrate the quick way to select a table we'll copy the NFC North table:

NFC North

We want to copy cells F12 to I16.

Create a new subroutine which begins by selecting the NFL 2017 worksheet and then refer to cell F12:

Start of sub

You need to refer to any cell which belongs to the table you want to reference. Usually, this will be the top left cell of the table.

 

You can now refer to the CurrentRegion property of the cell:

CurrentRegion

CurrentRegion is a property which returns a reference to the table to which the referenced cell belongs.

 

To demonstrate which range of cells you're referring to, apply the Select method at the end of the instruction:

Select

Apply the Select method as shown here.

 

Run the subroutine and check which cells have been selected:

Result

The code selects the entire table to which cell F12 belongs.

You can now edit the code so that it copies the cells rather than selecting them:

Copy

Copy the cells to the NFC North worksheet.

Run the subroutine and check the results in Excel:

Results

You should find the copied data on the NFC North worksheet.

 

Finding the Bottom Corner of a Table

To practise using the End property to find the bottom corner of a table:

  1. Using the same module, create a new subroutine which starts by selecting the NFL 2017 worksheet:

Sub Copy_AFC_South()

 

Worksheets("NFL 2017").Select

 

End Sub

  1. Add an instruction which begins by referring to cell A19:

Sub Copy_AFC_South()

 

Worksheets("NFL 2017").Select

 

Range("A19",

 

End Sub

  1. Extend the instruction to set the Cell2 parameter to reference the cell at the bottom of the list from cell A19:

Sub Copy_AFC_South()

 

Worksheets("NFL 2017").Select

 

Range("A19", Range("A19").End(xlDown)

 

End Sub

  1. Apply the End property again to refer to the cell in the bottom right corner of the table:

Sub Copy_AFC_South()

 

Worksheets("NFL 2017").Select

 

Range("A19", Range("A19").End(xlDown).End(xlToRight))

 

End Sub

  1. Use the Copy method to copy the referenced cells to the correct location:

Sub Copy_AFC_South()

 

Worksheets("NFL 2017").Select

 

Range("A19", Range("A19").End(xlDown).End(xlToRight)).Copy _

Worksheets("AFC South").Range("A1")

 

End Sub

  1. Run the subroutine and check that the results appear in the correct place.
  2. Use the same technique to create another subroutine which copies the NFC South table to the correct worksheet:

Sub Copy_NFC_South()

 

Worksheets("NFL 2017").Select

 

Range("F19", Range("F19").End(xlDown).End(xlToRight)).Copy _

Worksheets("NFC South").Range("A1")

 

End Sub

  1. Run the subroutine and check that the results appear in the correct worksheet.

A Quick Way to Select a Table

To practise using the CurrentRegion property to refer to a table:

  1. Create a new subroutine which begins by selecting the NFL 2017 worksheet:

Sub Copy_AFC_West()

 

Worksheets("NFL 2017").Select

 

End Sub

  1. Add an instruction which refers to the CurrentRegion of cell A26:

Sub Copy_AFC_West()

 

Worksheets("NFL 2017").Select

 

Range("A26").CurrentRegion

 

End Sub

  1. Extend this instruction to apply the Copy method to the table and copy it to the correct worksheet:

Sub Copy_AFC_West()

 

Worksheets("NFL 2017").Select

 

Range("A26").CurrentRegion.Copy _

Destination:=Worksheets("AFC West").Range("A1")

 

End Sub

  1. Run the subroutine and check that the results appear in the correct worksheet.
  2. Create another subroutine which uses the same technique to copy the NFC West table:

Sub Copy_NFC_West()

 

Worksheets("NFL 2017").Select

 

Range("F26").CurrentRegion.Copy _

Destination:=Worksheets("NFC West").Range("A1")

 

End Sub

  1. Run the subroutine and check that the results appear in the correct worksheet.
  2. Save and close the workbook.
This page has 0 threads Add post