Solved: What Are the General Rules for the End Property?

[Cyberdude]

I have been trying to come up with the rules for using the ?End? property (End(xlUp), End(xlToRight), etc.), and I?m having trouble generalizing. I used a range that looks like:

cols-> K L M N O P Q ? IV
vals-> b b M N O P b
where ?b? is a blank, letters shown are the columns, and using row 5, then executed the following statements:

Range("K5").End(xlToRight).Select?...selects M5??.K L M N O P Q ? IV

Range("L5").End(xlToRight).Select?...selects M5??.K L M N O P Q ? IV

Range("M5").End(xlToRight).Select?...selects P5??.K L M N O P Q ? IV

Range("N5").End(xlToRight).Select?...selects P5??.K L M N O P Q ? IV

Range("P5").End(xlToRight).Select?...selects IV5? .K L M N O P Q ?IV

The Help writeup says End ?Returns a Range object that represents the cell at the end of the region that contains the source range.? My tests don?t seem to confirm this statement.

When I begin with a cell that contains a null/blank value (column K or L), the statement selects ?M?, which is not in the same region as the source range. Help defines a region as ?a range bounded by any combination of blank rows and blank columns.? With this definition of region, then it would seem that columns K and L aren?t in a region.

Next, when I begin with the cell at the end of the region (?P?), it selects the end of the following series of blanks. I would have expected it to select itself, since P is the end of the region that contains the source range.

Finally, Help gives the following example which ?extends the selection from cell B4 to the last cell in row four that contains data?.

Range("B4", Range("B4").End(xlToRight)).Select

When I ran this example I got the same results as when I ran the following statement:

Range("B4").End(xlToRight)).Select

From the description, I assumed that instead of selecting one cell it would select the whole range from ?B4? to the end of the region. It didn?t seem to do that. It just selected the cell at the end of the region.

[Jacob Hilderbrand]

Using End is the same as holding Control and using the arrows. If you are on an empty cell, you would skip all empty cells and go to the first cell that is not empty. And it you are on a cell that is not empty you would skip all those and go to the end, stopping at the last cell that is not empty.

[mdmackillop]

Hi Sid,
"Range("B4").End(xlToRight)).Select" contains an extra end bracket. Running both lines gives me two different results as expected.

[Cyberdude]

Malcolm, I just discovered that. I wrote the statement incorrectly. When I did it correctly, then I did indeed get a multiple cell selection just as advertized.

DRJ, if you will review my last example statement using P5 as the starting point, "End" takes me to the blank cell ("IV5") at the end of the sequence of blank cells following P5. As I said, to be consistent, starting at P5 should result in a selection of P5 since that is the end of the list of valued cells.

And if you are on a cell that is not empty you would skip all those and go to the end, stopping at the last cell that is not empty. P5 is a non-empty cell that just happens to be the end of the list. By your rule, shouldn't it select itself??

I think probably the rule should state that the search always begins in the region that begins with the cell following the base cell. However that wouldn't explain the behaviour of my first two examples beginning with "K5" or "L5". I may want to amend that to say that the search begins in the first "valued" region following the base cell. I have to do some more testing.

Later:
OK, here's the set of rules I've come up with (or should I say, the set of rules with which I've come up??):

1. If the base cell is a blank cell, then the cell selected will be:
a. The first valued cell following the base cell in the direction of search, or
b. If no valued cells are found, then the cell in the maximum or minimum allowable row or column in the direction of search (rows "1" or "65536" or columns "A" or "IV" in Excel 2003).

2. If the base cell is a valued cell that precedes the last valued cell in that region, then the cell selected will be the last valued cell in that region.

3. If the base cell is a valued cell that is the last valued cell in a region, then the cell selected will be either:
a. The first valued cell following the base cell, or
b. If no valued cells are found, then the cell in the maximum or minimum allowable row or column in the direction of search (rows "1" or "65536" or columns "A" or "IV" in Excel 2003).

[Jacob Hilderbrand]

Well normally End will take you to a cell with a value, but if you end up at the first or last column or row, it will stop.

So for your example of P5, End(xlToRight) would go to the first cell with a value, but it reaches IV5 without a value so it just returns the last column.

[Cyberdude]

Hey, drj, did you see the rules I came up with at the bottom of my last post? I'm curious about whether or not they seem to be correct. "End" has rather weird behaviour, and at times, seemingly inconsistent. It's kinda hard to write down rules that predict what it does. It's for sure that Help didn't do a very good job of it, although their example showing how you can select a range of cells with "End" in addition to being able to select a single cell is interesting.

[Jacob Hilderbrand]

I didn't see your last post before now. Basically think of it like this.

Consider a group of contiguous cells that have values a region. Now there can be many regions in the row or column you are working with.

When you use End, if you are in a region you will go to the start or end of the region, depending on which direction you are going. If you are not in a region you will go to the beggining or end of the next region, again depending on what direction you are going.

Now, the final thing to consider is that if you make it to the end or beginning of the worksheet (row 1, 65536 and col A, IV) you will get that cell even if it is not part of the next region (since there is no more space).

[Cyberdude]

When you use End, if you are in a region you will go to the start or end of the region, depending on which direction you are going.
But an exception to that rule would seem to be when the base cell is the last cell in the region. As I noted before,
Range("P5").End(xlToRight).Select
where "P5" is the rightmost cell in a horizontal region. This will not select itself ... it will go looking for the next region.

[Cyberdude]

I'd like to wrap this topic up by saying that the "End" property can be used to select not only a single cell or a horizontal or vertical array of cells, I've discovered that it can also select a rectangular array of cells:

Range("Q40", Range("Q40").End(xlDown).End(xlToRight)).Select

Thanks for all the comments guys.

[Jacob Hilderbrand]

I also use it a lot to get the last row for a column or row when looping, or when I need to determine a range (for a dynamic check for example).

LastRow = Range("A65536").End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column