MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

last row

  Asked By: Jai    Date: Nov 13    Category: MS Office    Views: 826

Does Excell have a code name for the very last row in the spreadsheet

Basically I want to write

If activecell= <name> then


Instead of

If activecell = 65536 then


Because I am worried about my code being used in the new 2007 excel



2 Answers Found

Answer #1    Answered By: Noah Evans     Answered On: Nov 13

: Does Excell have a code  name for the very last row  in
: the spreadsheet


Or just:


Make sure you are in a spreadsheet, not chart.

Answer #2    Answered By: Candace Foster     Answered On: Nov 13

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

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.


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, _

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _

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

Didn't find what you were looking for? Find more on last row Or get search suggestion and latest updates.