SOLVED

Vertical Scroll encompasses a million rows, most of which are blank

Copper Contributor

Hello,

I have about 1400 rows with data, about 1000 of which are hidden most of the time.  If I pull the vertical scroll box to the bottom of the vertical scroll space, it gets to Row 400,000 - or more.  (Sometimes it gets beyond Row 1,000,000.)  For this reason, if I move the vertical scroll box just a little, it still scrolls down thousands of rows.  I would like the vertical scroll space to reflect my USED unhidden rows, not the entirety of possible rows.  That way I can move the vertical scroll bar box more effectively.  

In case it matters, I have only about a dozen columns with data.  I use the spreadsheet in SharePoint, but it does the same thing even when I save it and use it as a freestanding Excel worksheet.  I tried to delete rows in case there is some errant data way down there, but I can't delete a million rows, at least I don't know how to do that without spending hours at it.

Any suggestions?

12 Replies
best response confirmed by Tracy7212 (Copper Contributor)
Solution

@Tracy7212 

Do the following in the desktop version of Excel.

Select the first entirely blank row by clicking on its row number.

Hold down Shift and drag the 'thumb' of the vertical scroll bar down until you see row 1048576.

Hold down Shift and click on the row number of row 1048576.

This will select everything from the first blank row to the bottom of the worksheet.

On the Home tab of the ribbon, click Clear > Clear All.

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type ActiveSheet.UsedRange and press Enter.

Switch back to Excel and save the workbook.

Does the vertical scroll bar now act normally?

@HansVogelaar 

 

It worked PERFECTLY.  Thank you so much!

That is genius solution (by you) and an hilarious UX fail (by MS).
I like your solution. Its worked amazingly @HansVogelaar.

@HansVogelaar 

Hello, I have the same problem, but only have Excel 365... no desktop version. I'm stuck at Alt+f11. Any thoughts?

Thanks!

@Cat_at_work2023 

Skip the instructions from Alt+F11 on.

Instead close and reopen the workbook.

Thank you! That worked! I appreciate your help.

YOU ARE A LIFE-SAVER... THANKYOU SO MUCH!  @HansVogelaar 

@HansVogelaar 

this was simple but genius, thank you! 

 

@HansVogelaar Hello! I am having the same issue, but columns instead of rows. I am not on the desktop version, it's a spreadsheet we are all sharing on OneDrive. Would these instructions apply for me as well? Also, what is a "thumb"? :( Sorry, not super excel savvy 

 

UPDATE! I googled what thumb was and was able to do it. It worked for me.

Thank you so much! 

One sheet in my ex el workbook has over one million empty rows. I have tried everything to delete them but to no avail. Pleasexhelp

@HansVogelaar 

The clear all does not appear to do anything at all all cells remain selected
1 best response

Accepted Solutions
best response confirmed by Tracy7212 (Copper Contributor)
Solution

@Tracy7212 

Do the following in the desktop version of Excel.

Select the first entirely blank row by clicking on its row number.

Hold down Shift and drag the 'thumb' of the vertical scroll bar down until you see row 1048576.

Hold down Shift and click on the row number of row 1048576.

This will select everything from the first blank row to the bottom of the worksheet.

On the Home tab of the ribbon, click Clear > Clear All.

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type ActiveSheet.UsedRange and press Enter.

Switch back to Excel and save the workbook.

Does the vertical scroll bar now act normally?

View solution in original post