Excel Visual Basic for Applications online training

This page has 2 threads | Add post

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

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:

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:

Begin your subroutine as shown here.

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

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:

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:

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:

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 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:

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:

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:

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:

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

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

Your subroutine should look like this one.

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

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 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:

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

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

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:

Apply the Select method at the end of the instruction.

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

This is the cell you should end up in.

To practise using the End property:

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

Sub This_Is_The_End()

Range("A4").Select

End Sub

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(xlToRight).Select

End Sub

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

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

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(xlToRight) _

.End(xlToRight).Select

End Sub

Run the subroutine and check that you end up in cell I30 again.
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

Run the subroutine to check that it works.
Save and close the workbook.

This page has 2 threads

Add a new post

From:	learningvba
When:	14 Oct 22 at 16:56

Thanks you Andrew!
I totally forgot about the completed file. So, my issue was that I had a line break between each ".End(xlDown) _". I thought this made no difference, but apparently it does :D

From:	Andrew G
When:	17 Oct 22 at 06:49

You're welcome, happy to hear that you fixed it!

From:	learningvba
When:	13 Oct 22 at 05:03

Hello Andy

On step 4, I tried writing the code exactly as displayed, also tried copying the code below.

But I get the error

"Compile error:

Invalid use of property"

I don't understand why...

Could you please help?

Sub This_Is_The_End()

Range("A4") _

.End(xlDown) _

.End(xlToRight) _

.End(xlToRight).Select

End Sub

From:	Andrew G
When:	13 Oct 22 at 08:24

Hi!

If you've copy pasted the code you may have extra blank lines which you'll need to delete. You can have a look at the completed answer file which shows how the code should look.

I hope that helps!

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

The Sample Data

Finding the Bottom of a List

Jumping Over Gaps

Moving Upwards

Chaining Together a Sequence of End Properties

Head office

London

Manchester