Like I said before - crazy idea. After some testing, it looks like the
function defaults to the last cell of the sheet after certain size of rango
or number of areas. The following code I found via Google (slightly
modified) does seem to work:

Function LastCell(Optional ws As Worksheet) As Range
If ws Is Nothing Then Set ws = ActiveSheet
Set rng = ws.Cells
Set LastCell = rng(1)
On Error Resume Next
Set LastCell = Intersect( _
rng.Find("*", rng(1), xlValues, xlWhole, xlByRows, _
xlPrevious).EntireRow, _
rng.Find("*", rng(1), xlValues, xlWhole, xlByColumns, _
xlPrevious).EntireColumn)
End Function

KL

"KL" wrote in message
...
Sorry, the line "For Each a In rng" should actually read "For Each a In
rng.Areas"

KL


"KL" wrote in message
...
Just a crazy idea (not fully tested). Should ignore any cells that do not
contain formulas or values (incl. text, numeric and logical).

Regards,
KL

'------Code Start-------
Function LastCell(Optional Ws As Worksheet) As Range
Dim consts As Range: Dim frmls As Range
Dim r As Single: Dim c As Integer
Dim rTemp As Single: Dim cTemp As Integer
Dim rng As Range

If Ws Is Nothing Then Set Ws = ActiveSheet
With Ws.Cells
On Error Resume Next
Set consts = .SpecialCells(xlCellTypeConstants)
Set frmls = .SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not frmls Is Nothing Then
If Not consts Is Nothing Then
Set rng = Application.Union(frmls, consts)
Else
Set rng = frmls
End If
ElseIf Not consts Is Nothing Then
Set rng = consts
End If
End With
r = 1: c = 1
For Each a In rng
rTemp = a.Cells(a.Rows.Count, a.Columns.Count).Row
cTemp = a.Cells(a.Rows.Count, a.Columns.Count).Column
If rTemp r Then r = rTemp
If cTemp c Then c = cTemp
Next
Set LastCell = Ws.Cells(r, c)
End Function

Sub test()
Dim mysheet As Worksheet
Dim myrng As Range

Set mysheet = Sheets(2)
Set myrng = LastCell(mysheet)
mysheet.Activate
myrng.Select
End Sub
'------Code End-------

"José Ignacio Bella" wrote in message
...
Hello Group, I have a problem with a macro, and maybe you can help me

I use a sentence 'ActiveCell.SpecialCells(xlLastCell).Select' to go to
the
last worksheet cell.
The problemis when this cell has been erased. Then it's not really the
last
cell, but Excel still points to this one.
Saving, closing and re-opening, then Excel points to the real last cell.

Do you know how to refresh the xlLastCell without closing the book?
Thanks in advance.