Automatically Resetting The Last Cell

Category: General VBA | [Item URL]

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