Module 2 - Moving Around in Excel
Lesson 2.2 - Working with Ranges
Topic 2.2.1 - The End Property

When you're working with lists or tables it's handy to be able to find the edges of the regions of data.  In this part of the lesson you'll learn how to move around a block of data using the End property.

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 completed code.

The Sample Data

To get started in this part of the lesson, extract and open the file linked to in the Files Needed section above:

Sample data

The workbook contains multiple tables showing results from the 2017-2018 NFL season. Fly, Eagles, fly!

Finding the Bottom of a List

You can use the End property to refer to a cell at the end of a list in one of four directions.  Commonly, you'll be using this technique to find the bottom of a list.  In the VBE, create a new module and begin a subroutine which starts by selecting cell A4:

Basic sub

Begin your subroutine as shown here.

 

Add a new instruction which refers to the End property of the ActiveCell:

End property

The icon next to the End keyword indicates that it is a property rather than a method.

 

You can apply one of four different directions to the End property.  Try typing an open parenthesis to see the list of options:

Directions

Open a set of round brackets to see the list of four directions (the tooltip obscures the first one).

 

In this case we want to go downwards so use the xlDown direction:

Down

You can either begin typing or use the cursor keys to select the xlDown direction.

 

The End property returns a reference to a Range object but doesn't do anything to the cell.  If we want to move to the cell at the bottom of the list we need to apply the Select method to it:

Select

You can apply any method or property to the cell returned by the End property.

 

When you run the subroutine you should find that you end up with cell A9 selected:

Cell A9

Cell A9 is the cell immediately above the next blank cell in the list.

 

Jumping Over Gaps

You can use the End property to leap over gaps between regions of data.  Using the same subroutine from the previous example, add a copy of the second line of code:

Down twice

Copy and paste the second line of code unless you'd like to practise typing!

 

When you run the subroutine this time, you'll end up in cell A12:

A12

Cell A12 is the first populated cell after the gap of empty cells.

 

Moving Upwards

You'll often want to find the last populated cell in a column which is difficult when the list of data contains gaps.  You can use the End property to go upwards from the very last row of a worksheet to get around this.  Create a new subroutine and refer to the last cell in column A on the worksheet:

Bottom cell

The bottom cell in column A is A1048576.

 

Take care if you're still using legacy .xls workbooks as the number of rows in a worksheet is much lower.  One way to always refer to the last cell in column A, regardless of the workbook's version is to use the Cells property and count the number of rows on the worksheet:

Cells(ActiveSheet.Rows.Count, 1)

Rather than selecting this cell, you can apply the End property directly to it:

Up

In this case, we'll use the xlUp direction.

 

You can then apply the Select method to the range returned by the End property:

Select

Your subroutine should look like this one.

 

When you execute the code you should find that cell A30 is selected:

A30

Chin up, Broncos fans. Perhaps Case Keenum can help make the difference this year.

 

Chaining Together a Sequence of End Properties

You can use a sequence of End properties in the same instruction in order to reach a destination cell.  You'll often use this technique to find the bottom-right corner of a table.  Create a new subroutine which begins by selecting cell A4:

Begin sub

Begin the subroutine like this.

 

Add a second instruction which begins by referring to the cell at the end of the list in the downwards direction:

End down

The second instruction will currently return a reference to cell A9.

 

You can then immediately apply another End property in the same instruction:

Second end

The second End property uses the xlToRight direction.

 

Take care that you use xlToRight and not simply xlRight with the End property.

You can then apply the Select method to the range returned by the sequence of End properties:

Select

Apply the Select method at the end of the instruction.

 

When you run the subroutine you should find that you have cell D9 selected:

End result

This is the cell you should end up in.

 

To practise using the End property:

  1. Using the same module, create a new subroutine which starts by selecting cell A4:

Sub This_Is_The_End()

 

Range("A4").Select

 

End Sub

  1. Now add multiple instructions, using the End property only once per instruction, to reach cell I30.  You'll need to go down seven times and to the right three times and it doesn't matter which order you do this in:

Sub This_Is_The_End()

 

Range("A4").Select

 

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

ActiveCell.End(xlDown).Select

 

ActiveCell.End(xlToRight).Select

ActiveCell.End(xlToRight).Select

ActiveCell.End(xlToRight).Select

 

End Sub

  1. Run the subroutine (or step through it using F8) and make sure that you end up in the correct cell:
Final cell

You should end up in this cell. It'll be interesting to see how Jimmy G performs over a whole season.

 
  1. Now edit the subroutine above so that you go from cell A4 to I30 using the End property ten times in one instruction (the sample code below uses continuation characters so that it fits on the page!):

Sub This_Is_The_End()

 

Range("A4") _

.End(xlDown) _

.End(xlDown) _

.End(xlDown) _

.End(xlDown) _

.End(xlDown) _

.End(xlDown) _

.End(xlDown) _

.End(xlToRight) _

.End(xlToRight) _

.End(xlToRight).Select

 

End Sub

  1. Run the subroutine and check that you end up in cell I30 again.
  2. Create a final subroutine which moves from the bottom cell in column I, upwards to reach cell I30:

Sub The_Quick_Way()

 

Range("I1048576").End(xlUp).Select

 

End Sub

  1. Run the subroutine to check that it works.
  2. Save and close the workbook.
This page has 0 threads Add post