Last Cell Resetter: Stop Ctrl+End From Sending You to Row 65,536

TL;DR: You press Ctrl+End and Excel sends you to row 50,000 on a sheet where the actual data ends at row 215. The scroll bar thinks the sheet has 500 pages. This macro finds the real last cell on every sheet, trims the empty rows and columns Excel mistakenly thinks are occupied, and saves the workbook. One click — every Ctrl+End in the file lands where it should.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook where Ctrl+End overshoots the actual data on one or more sheets
• Save before running — the macro saves the workbook to persist the reset

Limitations:

• Deletes rows below and columns to the right of the actual last data cell — if those areas contain formatting or stray values you want to keep, save a copy first
• Does not reset the print area (that’s a separate setting under Page Layout)
• May not fully shrink UsedRange on sheets with objects (shapes, charts, comments) extending past the last data cell — remove those objects first
• Requires the workbook to have been saved at least once (uses ThisWorkbook.Save)

#The Macro

Option Explicit

Sub ResetLastCell()
    ' ── Last Cell Resetter ────────────────────────────
    ' Resets the "last cell" on every sheet to the
    ' actual last-used cell. Fixes Ctrl+End jumping
    ' past real data. Reports old vs. new address for
    ' every adjusted sheet. Saves the workbook to
    ' persist the reset across reopens.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim usedCell As Range
    Dim realLastRow As Long, realLastCol As Long
    Dim oldLastRow As Long, oldLastCol As Long
    Dim oldAddr As String, newAddr As String
    Dim report() As String, count As Long, i As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    count = 0
    ReDim report(1 To ThisWorkbook.Worksheets.Count)

    For Each ws In ThisWorkbook.Worksheets
        ' ── Current UsedRange bounds ───────────────────
        Set usedCell = ws.UsedRange
        oldLastRow = usedCell.Row + usedCell.Rows.Count - 1
        oldLastCol = usedCell.Column + usedCell.Columns.Count - 1

        ' ── Find actual last cell with data ────────────
        On Error Resume Next
        realLastRow = ws.Cells.Find("*", ws.Cells(1, 1), _
            xlFormulas, xlPart, xlByRows, xlPrevious).Row
        If Err.Number <> 0 Then realLastRow = 1
        Err.Clear

        realLastCol = ws.Cells.Find("*", ws.Cells(1, 1), _
            xlFormulas, xlPart, xlByColumns, xlPrevious).Column
        If Err.Number <> 0 Then realLastCol = 1
        On Error GoTo CleanUp

        ' ── Reset if UsedRange overshoots ──────────────
        If oldLastRow > realLastRow Or oldLastCol > realLastCol Then
            oldAddr = ColLetter(oldLastCol) & oldLastRow
            newAddr = ColLetter(realLastCol) & realLastRow

            If oldLastRow > realLastRow Then
                ws.Rows(realLastRow + 1 & ":" & oldLastRow).Delete
            End If
            If oldLastCol > realLastCol Then
                ws.Columns(ColLetter(realLastCol + 1) & _
                    ":" & ColLetter(oldLastCol)).Delete
            End If

            count = count + 1
            report(count) = "'" & ws.Name & "' " & _
                oldAddr & "→" & newAddr
        End If
    Next ws

    ' ── Save to persist the reset ──────────────────────
    ThisWorkbook.Save

    ' ── Report ─────────────────────────────────────────
    If count = 0 Then
        MsgBox "All " & ThisWorkbook.Worksheets.Count & _
               " sheet(s) are correct — no last cell " & _
               "needed resetting.", vbInformation, _
               "Last Cell Resetter"
    Else
        Dim msg As String
        msg = count & " sheet(s) adjusted:" & vbCrLf & vbCrLf
        For i = 1 To count
            msg = msg & "  " & report(i) & vbCrLf
        Next i
        msg = msg & vbCrLf & "Workbook saved. Ctrl+End now " & _
              "stops at the real last cell."

        MsgBox msg, vbInformation, "Last Cell Resetter"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: column number → letter ────────────────────
Private Function ColLetter(ByVal colNum As Long) As String
    ColLetter = Split(Cells(1, colNum).Address(True, False), "$")(0)
End Function

#How It Works

#Two different last-cell concepts

Excel tracks two ideas of “the last cell.” The first is the UsedRange — a rectangle from A1 to the furthest cell Excel has ever touched with data, formatting, or a formula. The second is the actual last cell — the furthest cell that currently contains a value or formula.

When you delete data, the UsedRange does not shrink. Format a cell in row 10,000, then clear it — Excel still considers row 10,000 as “used.” This is why Ctrl+End overshoots, why the scroll bar is tiny, and why file sizes balloon. The Find("*", ... xlPrevious) trick finds the real last cell regardless of what UsedRange says.

#Why .Find("*") works when .UsedRange lies

Find searches the entire sheet for any character (the * wildcard) in reverse direction — effectively asking “what’s the cell furthest down and furthest right that actually contains something?” It ignores formatting residue and looks only at content. The two-pass approach (first xlByRows, then xlByColumns) is necessary because the furthest-right cell and the furthest-down cell are not always the same cell.

realLastRow = ws.Cells.Find("*", ws.Cells(1, 1), _
    xlFormulas, xlPart, xlByRows, xlPrevious).Row

If the sheet is completely blank, Find returns Nothing and the .Row access raises an error. The On Error Resume Next + Err.Number check defaults to row 1 / column 1 — an empty sheet’s “last cell” is A1.

#Empty rows are deleted, not just cleared

Simply clearing cells past the data boundary does not shrink UsedRange. Excel continues to hold the metadata that the cell was once used. Row deletion is the only reliable reset:

If oldLastRow > realLastRow Then
    ws.Rows(realLastRow + 1 & ":" & oldLastRow).Delete
End If

This deletes entire rows and entire columns. It means any stray formatting or comments in the excess area are lost — which is exactly the goal. If you had something meaningful in row 5,000, the Find("*") call would have found it and realLastRow would be 5,000. Nothing of value is deleted.

#Saving is part of the macro

ThisWorkbook.Save

Without a save, the UsedRange reset is held in memory but may revert when the workbook is closed and reopened. Saving writes the corrected range to disk. The macro saves automatically to avoid the “it worked, then I reopened and it was broken again” loop.

If you want to test without saving, comment out this line. The reset will hold for the current session but revert on reopen.

#Named vs. unnamed reporting

The message box lists every adjusted sheet by name with the old and new last cell addresses:

3 sheet(s) adjusted:

  'Sched-A Income' XFD505→G12
  'Fixed Assets' Z800→H22
  'Depreciation' XFD1048576→R18

Workbook saved. Ctrl+End now stops at the real last cell.

The XFD501 → G12 format tells you exactly how much bloat was removed. XFD1048576 (the very last cell in modern Excel) appears on sheets where someone pressed Ctrl+End in a brand-new sheet, or where a copy-paste from an external source dragged the used range to the sheet boundary.

#Adapt It