it happens in all version :(

however when working with the last cell (in vba) be aware of following:

calling the UsedRange property of the sheet recomputes the lastcell.
not vice versa.

be aware that usedrange not only looks at data but also at cell
formatting, and that excel may set a cell's format without you being
aware of it (like when inserting dates)


to be sure you're using a "fresh" lastcell, precede your code
with a (dummy) call to UsedRange.

temp$=activesheet.usedrange.address
activesheet.specialcells(xlLastCell).select

all said and done, many programmers will avoid using the lastcell and/or
usedrange, mainly because of the formatting issues.

Many questions in this group relate to cell manipulation AND many will
show ingenious method for finding the last cell WITH DATA in a specific
area.

Try following function (note that it cant be used as a worksheetfunction
as it uses an "active method", but it can be used in any vba procedure)

usage like:
LastCell(activesheet.cells).select
or
set r = LastCell([sheet3!e100:z500])

Function LastCell(r As Range) As Range
Set LastCell = r(1)
On Error Resume Next
Set LastCell = Intersect( _
r.Find("*",r(1),xlValues,xlWhole,xlByRows,xlPrevio us).EntireRow, _
r.Find("*",r(1),xlValues,xlWhole,xlByColumns,xlPre vious).EntireColumn)
End Function




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"jim c." wrote:

in readin an article at

http://j-walk.com/ss/excel/tips/tip73.htm
Automatically Resetting the Last Cell

everything i have read about excel's problem of resetting
the last cell refers to excel 97.

im running excel2000 winxp and have this problem too.
(including all office, winxp updates)
i've even selected the entire sheet and cleared it

Worksheets(2).Cells.Clear

this still doesnt work...

the solution in the article works fine.
Does anyone else have this problem with excel 2000?