Blank cells are not clearing, why!?

[rpm567]

If I hit ctrl + end, I end up half a million rows past my data. I go to my last data point, select all the rows from past my data, shift+ctrl+down arrow, delete, clear all, remove rows, save, close, reopen. But once again, ctrl + end and I'm at the same spot. No formulas calling past my data, no formatting, nor any named ranges. Anything to do with this sheet causes a near collapse of excel and the file size has jumped up 10x. Anything I'm missing? Any help would be greatly appreciated

 

[shg]

Select all of the rows below your data and do Clear > Clear All. That keeps formatting that may extend the used range from copying from the last row when you delete rows.

Then delete the rows.

Then repeat for the columns to the right of your data.

Then save, close, and reopen.

 

[Joe4]

Any VBA or named ranges?

 

[rpm567]

Any VBA or named ranges?

There was a ref error in a named range in that sheet. That fixed it. Thanks!!!

 

[Joe4]

Yes, if your named range was referencing one of the rows that was deleted, that could cause that REF error you were seeing.