Automatically Resetting the Last Cell
It's commonly known that Excel has a problem in keeping track of the "last cell" in a worksheet.
Aran Dunkley (from Auckland, New Zealand) discovered that any VBA reference to the UsedRange.Rows.Count property will force Excel to recognize the updated last cell on a worksheet. Therefore, if you're using Excel 97 you can insert the following subroutine into the code module for the ThisWorkbook object:
Private Sub Workbook_SheetSelectionChange _ (ByVal Sh As Object, ByVal Target As Excel.Range) x = Sh.UsedRange.Rows.Count End Sub
This subroutine is executed whenever the selection is changed on a worksheet. It quickly accesses the Count property -- and the last cell is reset. Pressing End-Home will always take you to the real last cell in the worksheet.
CAUTION: This technique has one potentially serious side-effect. Executing this procedure wipes out Excel's undo stack. In other words, if you use this technique you won't be able to use Undo. Therefore, a better technique might be to reset the used range when the workbook is saved (saving a workbook also zaps the undo stack). Here's an example:
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each Sh In ThisWorkbook.Worksheets
x = Sh.UsedRange.Rows.Count
Next Sh
End Sub
Thanks to Aran for discovering this technique and allowing me to share it. And thanks
to Roger Gwynne-Jones for pointing out the Undo problem.