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