Monday, November 29, 2004

Identifying the last cell in Excel

There are a number of ways to do this.

CTRL-END is the quickest way to locate the Last Cell in an Excel spreadsheet. Using F5 - Special - Last Cell is the actual command that CTRL + END performs.
This method 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.

Using VBA there are a number of methods as discussed below.
The Worksheet object's UsedRange does not always work for the same reason as the spreadsheet techniques described above.

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.
There may be memory issues related to this (associated with the real Last Cell).

Even so, there is a an even better way. The procedure below was written by Rodney Powell Microsoft MVP - Excel and adapted from a procedure written by MVP, Bob Umlas. They have tested it thoroughly and believe it's the most reliable way to accomplish this.

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:

Sub Demo()

MsgBox LastCell(Sheet1).Row

End Sub


Posted by: Jethro | 1:29 AM |