Error in finding last used cell in Excel with VBA

Question

When I want to find the last used cell value, I use:

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

I am getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What's the reason behind this?

Answer 1

Note: this answer was motivated by this comment. The purpose of UsedRange is different from what is mentioned in the answer above.

As to the correct way of finding the last used cell, one has first to decide what is considered used, and then select a suitable method. I conceive at least three meanings:

1. Used = non-blank, i.e., having data.

2. Used = "... in use, meaning the section that contains data or formatting." As per official documentation, this is the criterion used by Excel at the time of saving. See also this. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one's work.

3. Used = "... in use, meaning the section that contains data or formatting" or conditional formatting. Same as 2., but also including cells that are the target for any Conditional Formatting rule.

How to find the last used cell depends on what you want (your criterion).

For criterion 1, I suggest reading this answer. Note that UsedRange is cited as unreliable. I think that is misleading (i.e., "unfair" to UsedRange), as UsedRange is simply not meant to report the last cell containing data. So it should not be used in this case, as indicated in that answer. See also this comment.

For criterion 2, UsedRange is the most reliable option, as compared to other options also designed for this use. It even makes it unnecessary to save a workbook to make sure that the last cell is updated. Ctrl+End will go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid).

For criterion 3, I do not know any built-in method. Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by UsedRange or Ctrl+End. In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by UsedRange. Accounting for this would require some VBA programming.

---

As to your specific question: What's the reason behind this?

Your code uses the first cell in your range E4:E48 as a trampoline, for jumping down with End(xlDown).

The "erroneous" output will obtain if there are no non-blank cells in your range other than perhaps the first. Then, you are leaping in the dark, i.e., down the worksheet (you should note the difference between blank and empty string!).

Note that:

1. If your range contains non-contiguous non-blank cells, then it will also give a wrong result.

2. If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.