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

Search for Tips

All Tips

Browse Tips by Category

Tip Books

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

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy