Automatically Resetting The Last Cell
It's commonly known that Excel sometimes has a problem in keeping track of the "last cell" in a worksheet. The last cell is lower right cell that contains data.
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
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks