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.
You can click here to download the file needed for this page.
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.
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:
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:
- 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:
- 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!):
- 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:
- Run the subroutine to check that it works.
- Save and close the workbook.