NOTE: I intend to make this a "one stop post" where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.
Some of the most common ways of finding last row which are highly unreliable and hence should never be used.
UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.
Type something in cell A5. Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10 red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won't be 5.
Here is a scenario to show how UsedRange works.
What would happen if there was only one cell (A1) which had data? You will end up reaching the last row in the worksheet! It's like selecting cell A1 and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.
CountA is also unreliable because it will give you incorrect result if there are blank cells in between.
And hence one should avoid the use of UsedRange, xlDown and CountA to find the last cell.
If you notice that we have a . before Rows.Count. We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using . before Rows.Count and Columns.Count. That question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. Similarly Columns.Count returns 256 and 16384, respectively.
The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.
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?
I created this one-stop function for determining the last row, column and cell, be it for data, formatted (grouped/commented/hidden) cells or conditional formatting.
This is my encapsulated function that I drop in to various code uses.
' Search for any entry, by searching backwards by Rows.
You need to enter the formula without brackets and then hit Shift + Ctrl + Enter to make it an array formula.
This will give you address of last used cell in the column D.
and if u are using its "a1,048,576"
Since the original question is about problems with finding the last cell, in this answer I will list the various ways you can get unexpected results; see my answer to "How can I find last row that contains data in the Excel sheet with a macro?" for my take on solving this.
6) involvement in a Table (List Object).
Which combination do you want to test for? Some (such as Tables) may be more difficult to test for, and some may be rare (such as a shape outside of data range), but others may vary based on the situation (e.g., formulas with blank values).
A) Can there be hidden rows (e.g. autofilter), blank cells or blank rows?
B) What kind of performance is acceptable?
C) Can the VBA macro affect the workbook or the application settings in any way?
A solution involving .End(xlUp) to search backwards from the end of a column will, just as CTRL+UP, look for data (formulas producing a blank value are considered "data") in visible rows (so using it with autofilter enabled might produce incorrect results ⚠️).
You have to take care to avoid the standard pitfalls (for details I'll again refer to the answer by Siddharth Rout here, look for the "Find Last Row in a Column" section), such as hard-coding the last row (Range("A65536").End(xlUp)) instead of relying on sht.Rows.Count.
.SpecialCells(xlLastCell) is equivalent to CTRL+END, returning the bottom-most and right-most cell of the "used range", so all caveats that apply to relying on the "used range", apply to this method as well. In addition, the "used range" is only reset when saving the workbook and when accessing worksheet.UsedRange, so xlLastCell might produce stale results⚠️ with unsaved modifications (e.g. after some rows were deleted). See the nearby answer by dotNET.
sht.UsedRange (described in detail in the answer by sancho.s here) considers both data and formatting (though not conditional formatting) and resets the "used range" of the worksheet, which may or may not be what you want.
Note that a common mistake ️is to use .UsedRange.Rows.Count⚠️, which returns the number of rows in the used range, not the last row number (they will be different if the first few rows are blank), for details see newguy's answer to How can I find last row that contains data in the Excel sheet with a macro?
More explicit solutions that check individual Cells' in a loop are generally slower than re-using an Excel function (although can still be performant), but let you specify exactly what you want to find. See my solution based on UsedRange and VBA arrays to find the last cell with data in the given column -- it handles hidden rows, filters, blanks, does not modify the Find defaults and is quite performant.
when using .Value (which is a Variant) avoid implicit casts like .Value <> "" as they will fail if the cell contains an error value.
I would add to the answer given by Siddarth Rout to say that the CountA call can be skipped by having Find return a Range object, instead of a row number, and then test the returned Range object to see if it is Nothing (blank worksheet).
Also, I would have my version of any LastRow procedure return a zero for a blank worksheet, then I can know it is blank.
This essentially returns the same cell that you get by Ctrl + End after selecting Cell A1.
A word of caution: Excel keeps track of the most bottom-right cell that was ever used in a worksheet. So if for example you enter something in B3 and something else in H8 and then later on delete the contents of H8, pressing Ctrl + End will still take you to H8 cell. The above function will have the same behavior.
Used = non-blank, i.e., having data.
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.
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!).
If your range contains non-contiguous non-blank cells, then it will also give a wrong result.
If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.