|
Identifying the Real Last Cell
Rodney POWELL
Microsoft MVP - Excel |
|
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
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
