How do I identify where 'phantom data' might exist in a worksheet (Ctrl+END returns very high row #)?

[d0rian]

I'm troubleshooting a bloated / laggy file, and noticed that when I press Ctrl+END, which I was told should move the cursor to the bottom- and right-most used cell of the sheet, it takes me to cell BV96570.
The BV column makes sense, because I do indeed have data in cells out to column BV, but the sheet only has ~3,500 rows of cells with data. So why does the sheet think that rows all the way down to 96,570 are "used"?
The weirdest thing is that if I highlight rows 4,000 - 100,000 and DELETE them, Ctrl+END still takes me to cell BV96570. In other words, I'm actively deleting anything that could be below row 4,000 and the sheet still thinks 96K+ rows are being 'used'.
What could be going on here?

 

[Gokhan Aycan]

Tried saving after deleting the rows?

 

[d0rian]

Tried saving after deleting the rows?

That did it, thanks. Knocked 500KB off the file size too. Excel's weird, man.
I'd still be interested to know how this happened in the first place, or why Excel thought there was data in those rows...doesn't matter too much at the end of the day i guess.

 

[Fluff]

It could be a number of things, but one of the common ones is cell formatting.

 

[d0rian]

It could be a number of things, but one of the common ones is cell formatting.

In my case it seems that it had to do with Named Ranges. I deleted the rows below 4000 and saved the file, and Ctrl+END then took me to BV4000 (good), and knocked 500KB off the file size (very good).
BUT, I realized that deleting those rows had re-mapped some of the Named Ranges I had created in the file, and cut their end row to 4000. E.g. Range "source_1" was edited from A1:A9999 to A1:A4000. I then manually re-mapped 'source_1' to A1:A9999, ran my book's macros, and then all of a sudden Ctrl+END took me to BV9999.

Note that it has something to do with the macros I ran, because Ctrl+END didn't go to BV9999 after the simple act of manually re-mapping source_1 (and saving the file). Note also that there was no actual data (or formatting as far as I could tell) below row 4000 after the manual re-map, and yet Excel recognized the 'bottom row' as 9999, which obviously has to do with the source_1 named range, because that's the specific row I used as the end row.

Don't have time to troubleshoot more now, but just posting for posterity in case someone else stumbles across this thread.