I've kept this bit of information for my use -

Identifying the Real Last Cell

This is among the most valuable of all VBA techniques in Excel development.
There always seems to be a need to locate the last record in a table of
data. The most commonly used ways for doing this have significant pitfalls.

The Worksheet object's UsedRange does not always work because the used range
(or "dirty area") of a spreadsheet  may be larger than the area actually
populated with your records.

The Range object's CurrentRegion property is not the best either because
non-contiguous or incomplete records can cause an inaccurate reading of the
number of used rows.

The Range object's End method fails whenever you use the xlDown argument if
it encounters a blank cell in the column  being evaluated. However, if you
use this same technique instead with the xlUp argument, starting from the
bottom of the worksheet or just beneath the used range, it is almost
bulletproof.

Even so, we've found even a better way. The sample procedure below is a
variation on a technique I learned from fellow Excel MVP, Bob Umlas. It has
been put to the test and I believe it's the very most reliable way to
accomplish this.

Example:

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data  in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function


Using this Function:

The LastCell function shown here would not be used in a worksheet, but would
be called from another VBA procedure. Implementing it is as simple as the
following example:

Example:
Sub Demo()

MsgBox LastCell(Sheet1).Row

End Sub