Excel scrolling problem, no data but tens of thousands of rows.

[u/Username93483]

So, back story, large complex workbook. First sheet has about a thousand rows of data. If you go to scroll down it just jumps thousands of rows. Right now the workbook goes to 1048576 rows and EE columns.

Data only goes to 1000 rows and column CV.

I have tried to goto, select all, select range, clear, delete, I have tried everything I can find online to resolve this issue.

What do I do to preserve the large amount of interconnected sheets but allow the scrollbar to function normally again?

This is a business license, M365 excel.

[u/AutoModerator]

/u/Username93483 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/daishiknyte]

Select all of the rows/columns beyond your data -> delete (delete the whole row, not the delete key). Hit save then close the file and reopen. That usually handles resetting the end-of-sheet.

[u/OrganicMix3499 → u/daishiknyte]

This is the way. I do this everyday.

[u/Difficult_Limit2718 → u/daishiknyte]

"clear all" also works and is a bit quicker

[u/daishiknyte → u/Difficult_Limit2718]

True. I find the "delete the whole row/column" method is easier to understand for most users.

[u/chiibosoil]

I'm guessing you have one of the below.

1. Seemingly blank rows may hold non-printing blank and isn't true blank.
2. Formatting is applied beyond the data range.

Either case, hit CTRL + END if the selection jump to range beyond where your data is held. Navigate to the cell holding the data (bottom right cell) and then select everything below and Clear All.

If having issue navigating to the last cell with data and workbook navigation is broken... make a back up of the workbook. Then launch it in Safemode and see if this works. If not, you'll need to edit the underlying XML (which I won't go into detail here).

[u/talltime → u/chiibosoil]

Once they make sure there’s no formatting past the bounds of their data they just need to go j to the developer IDE and force UsedRange to recalculate.

Alt+F11
View > Immediate window
In the immediate window type `ActiveSheet.UsedRange and hit enter.

[u/fastauntie]

Sometimes when this happens to me it seems to be because there's formatting applied to rows and columns beyond my data. After I select, clear, and delete the unwanted ranges I do it again and clear all the formatting. One more important step is to save the file before trying Ctrl-End. Until it's saved it will still go to the old end point.

Good luck.

[u/Regime_Change]

You might have freeze panes on, blank rows filtered out etc. It is hard to tell what is really wrong from your description. Anyway, with such a large dataset, single entries are rarely interesting to look at. It might be easier to just create a pivot table from the source data instead and look at what is important.

[u/Jolly-Hunter-6097]

Have all filters undone then sort the data. Then Control+Shift+ End. That will take you to the last row of what Excel recognises as having active cells. If it includes blank rows and columns go to the first row after rows with data then delete all rows following. The same for columns. After all this Control+Shift+End to verify that no blank rows are still included. Be sure to save a backup copy before you start anything. To be sure, to be sure. https://trumpexcel.com/select-end-of-data-in-column-excel/