Hi Dave

I have not tested it thouroughly, but it looks like the line

Set DummyRange = Sheets(SomeName).UsedRange

resets UsedRange for the corresponding sheet.

Try this code:

Sub TestForLastCell()
'Leo Heuser, 25 Apr. 2004
'In an empty sheet enter data in J12 and M20
'and run TestForLastCell from that sheet
Dim Sh As Worksheet

Set Sh = ActiveSheet

'LastCell is M20
MsgBox "Last cell is " & Sh.Cells.SpecialCells(xlLastCell).Address

Sh.Rows("13:20").Delete

'LastCell should now be J12, but...
MsgBox "Last cell is still " & Sh.Cells.SpecialCells(xlLastCell).Address

Call ResetUsedRange(Sh)

'UsedRange is reset and LastCell is correct (J12)
MsgBox "Last cell is now " & Sh.Cells.SpecialCells(xlLastCell).Address

End Sub

Sub ResetUsedRange(Sh As Worksheet)
Dim DummyRange As Range
Set DummyRange = Sh.UsedRange
End Sub

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Dave Peterson" skrev i en meddelelse
...
And sometimes hitting ctrl-end or using Leo's code will go further down or
to
the right than you expected.

You can try to reset the last cell by using techniques at Debra
Dalgleish's
site:
http://www.contextures.com/xlfaqApp.html#Unused