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?

I suppose everyone does!

I have found the solution to reset the last cell is to
save the file after the deletions.

Nikos Y.
-----Original Message-----
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?
.

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?

I almost never use xlLastCell (for that reason), but this may work
for you instead:

Public Function LastCell(Optional wkSht As Worksheet) As Range
If wkSht Is Nothing Then set wkSht = ActiveSheet
With wksht.UsedRange
Set LastCell = .Cells(.Count)
End With
End Sub

Call as

Dim myCell As Range
set myCell = LastCell()

or

Dim myAddr As String
myAddr = LastCell().Address

or

Dim myVal As Double
myVal = LastCell(Sheets("Sheet2")).Value

or even (though I strongly urge you to .Select only when absolutely
necessary):

LastCell().Select


In article ,
"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?