Deleting Rows in Excel Doesn't Reset Last Cell (Used Range)

Question

I have an Excel file that is larger than 7MB. To understand where the weight is coming from, I've used the method described in this SU question.

I narrowed most of the weight down to one sheet, and did ctrl+End to check what is the last cell in that sheet. Oddly enough, that cell was in the last possible row in the sheet (i.e. row 1048576).

I then proceeded to clear the formatting from all of the unused rows, and then - just in case - selected all the blank rows (beneath my data) using alt+Shift+↓ and alt+Shift+→, then right-click and delete.

However, after saving the file, closing it, and opening it up again - pressing ctrl+End still throws me to the last cell.

I've read up on the VBA equivalent of doing this manually, and it seems that this SO Question refers to the same thing - the Worksheet.UsedRange property.

Before I dive into VBA - is there something I can do manually to fix this?

P.S. Before marking this as a duplicate, note I've intentionally exhausted other options in my search for an answer.

EDIT:

To clarify, I've seen this answer (and tried it) and this KB article (and tried it as well) - no cigar. Same with this more recent answer.

Accepted Answer

I appreciate the the extra effort you've taken to document your efforts. Thank you.

I frequently reset bloated books using the methods that don't work for you. There are only two differences I can identify:

The first is that I've never tried to right-click -> delete. I'm a keyboard guy so I've always used Alt+E+D. That shouldn't matter one bit, but stranger things have happened.

The second difference is that I've never been stuck like you, it always resets after saving. Big help there, right?

A few possible solutions follow:

Must Read

Horizontal/Vertical Bloats

Today I had an issue where my VBA merged sheets vertically and my worksheet inexplicably bloated horizontally. I was initially confused and concerned because I've only had vertical bloat and didn't think to check horizontally, until my reset attempt failed twice. Please humor me for the sake of completeness and to keep that from happening to you:

Begin by closing any other Excel sessions, so the current workbook is the only workbook open.
Go to the of your data on the right side and select the first empty column - the whole column.
Ctrl+Shift+→.
Alt+E, then D.
Ctrl+Shift+End.
Alt+E, then D.
Ctrl+Home
Scroll to the bottom of your data
Select the first empty row - the whole row.
Ctrl+Shift+↓.
Alt+E, then D.
Ctrl+Shift+End.
Alt+E, then D.
Ctrl+Home
Ctrl+S

Yes, the two Ctrl+Shift+End lines are redundant, but I think your can survive the 15 keystrokes.

You "shouldn't" need to close your workbook after saving it, but I won't advise against it. Give it a shot. If that doesn't work, well - try VBA real quick.

Deleting Rows in Excel Doesn't Reset Last Cell (Used Range)

9 Answers

Must Read

Horizontal/Vertical Bloats

Chloe Bennett