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.
I'll start by expanding on the answer by sancho.s and the comment by GlennFromIowa, adding even more detail:
[...] one has first to decide what is considered used. I see at least 6 meanings. Cell has:
- 1) data, i.e., a formula, possibly resulting in a blank value;
- 2) a value, i.e., a non-blank formula or constant;
- 3) formatting;
- 4) conditional formatting;
- 5) a shape (including Comment) overlapping the cell;
- 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).
Other things you might want to consider:
With that in mind, let's see how the common ways of getting the "last cell" can produce unexpected results:
.End(xlDown)
code from the question will break most easily (e.g. with a single non-empty cell or when there are blank cells in between) for the reasons explained in the answer by Siddharth Rout here (search for "xlDown is equally unreliable.") Count
ing (CountA
or Cells*.Count
) or .CurrentRegion
will also break in presence of blank cells or rows .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?
.Find
allows you to find the last row with any data (including formulas) or a non-blank value in any column. You can choose whether you're interested in formulas or values, but the catch is that it resets the defaults in the Excel's Find dialog ????, which can be highly confusing to your users. It also needs to be used carefully, see the answer by Siddharth Rout here (section "Find Last Row in a Sheet")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.Whatever solution you pick, be careful
Long
instead of Integer
to store the row numbers (to avoid getting Overflow
with more than 65k rows) andDim ws As Worksheet ... ws.Range(...)
instead of Range(...)
).Value
(which is a Variant
) avoid implicit casts like .Value <> ""
as they will fail if the cell contains an error value.