Prevent Excel bloating

Excel has a problem that results in the last active cell can be far away from the last cell with anything in it.

Start with a clean Excel sheet and add some values to it. Press CTRL+End and you will in the case below end up in cell B2.

Now add a value to cell D4. Then delete that value and press CTRL+End and you will end up in cell D4. After using an Excel sheet for quite a while the last active cell could be on a row far far below.

One way is to manually delete the last rows and columns. In the above case deleting column C and D and row 3 and 4.

This can be solved with some VBA code as well.

The code below will solve this on the active sheet. One side effect is that the undo history is wiped out. The code can be run in the Immediate window in the Visual Basic editor (ALT+F11).

Application.ActiveSheet.UsedRange

Here is a way to do it automatically on all sheets when a workbook is saved.

Private Sub Workbook_BeforeSave _
  (ByVal SaveAsUI As Boolean, Cancel As Boolean)
    For Each ws In ThisWorkbook.Worksheets
        ws.UsedRange
    Next ws
End Sub