Forum Discussion
Tracy7212
Aug 31, 2020Copper Contributor
Vertical Scroll encompasses a million rows, most of which are blank
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 m...
- Aug 31, 2020
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
Aug 31, 2020MVP
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?
- laura_i2024Sep 29, 2024Copper Contributor
HansVogelaar - your solution fixed the problem when I view the excel sheet in the browser view however when I re-open it in desktop (after applying your solution and saving) I still have the same issue.
Any thoughts?
- Sep 30, 2024If you suspect there are extra blank rows in your spreadsheet beyond what you're actually using, you can easily determine it by pressing Ctrl+End. This will take you to the "last cell". If this is on a row far beyond what you think it should be, then you can follow the instructions mentioned earlier to get rid of the extra rows. You can also press F5 (Go To), then press Special, and choose Last Cell and press OK.
- Strin9beanJul 03, 2024Copper ContributorThe clear all does not appear to do anything at all all cells remain selected
- Strin9beanJul 03, 2024Copper Contributor
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
- momusMar 19, 2024Copper Contributor
- BobShermanMar 17, 2024Copper Contributor
YOU ARE A LIFE-SAVER... THANKYOU SO MUCH! HansVogelaar
- Cat_at_work2023Oct 02, 2023Copper Contributor
Hello, I have the same problem, but only have Excel 365... no desktop version. I'm stuck at Alt+f11. Any thoughts?
Thanks!
- HansVogelaarOct 02, 2023MVP
- JRodriguez22Jun 25, 2024Copper Contributor
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!
- aali2688Mar 09, 2023Copper ContributorI like your solution. Its worked amazingly HansVogelaar.
- simesyMay 13, 2022Copper ContributorThat is genius solution (by you) and an hilarious UX fail (by MS).
- Tracy7212Aug 31, 2020Copper Contributor