Excel 2007 Scroll-bar going to end of sheet
August 25, 2013 3:00 PM   Subscribe

I have an excel 2007 spreadsheet in which the vertical scrollbar covers the whole 1,000,000 rows i.e pressing ctrl - end takes me to row 1,048,576. I would like it to only scroll to the end of my data.

Clearing all the rows below my data does nothing.
I've tried changing the scroll area but that just stopped it going all the way down the bar was the same size

Last time this happened I just copy pasted to a new sheet but I don't want to have to update all the formulas again

Thanks in advance for your help.
posted by mataboy to Computers & Internet (9 answers total)
It does kind of sound like the file has gotten messed up.

What happens if you select the whole sheet and then copy and paste it to a new sheet? That should preserve the formulas and the data.
posted by gjc at 3:09 PM on August 25, 2013

Delete all the rows below your data (don't just clear them -- delete them) -- and then SAVE. The row count won't re-set until you save.

That said though -- if you are below your bottom row of data, ctrl+down will always take you to that very last row, even if you don't have those rows there.
posted by brainmouse at 3:10 PM on August 25, 2013 [1 favorite]

Select all the rows below your last row of data and HIDE them. (Right click -> Hide).
posted by pravit at 3:20 PM on August 25, 2013

brainmouse has it. Obviously, make sure the active cell is one with data in when you save. It might also be necessary to go to the right of your data and select all the way to the right and bottom and delete if that still fails.
posted by ambrosen at 3:26 PM on August 25, 2013

You have to actually delete the rows. Even though they seem empty they contain some kind of data, perhaps cell formatting has been added/removed which causes them to retain data. I'd also bet your file size is way larger than it should be.

You can fix it by either highlighting the rows and columns without data and deleting them (right-click, delete row, or ALT+E+D), or if it's easier, copy the data you do want and paste it into a completely new workbook. Just ensure you only highlight the desired data before you copy. Don't highlight entire rows or columns.
posted by odinsdream at 3:30 PM on August 25, 2013

I tried deleting all the rows but got an error saying:
"not enough available resources to complete this action".

When I delete the bottom 5 rows ctrl-end still takes me to the same place, shouldn't it go to the last row that wasn't deleted?

I think I may end up copying it to a new sheet. I was just hoping there was an easier way for the next time it happens.
posted by mataboy at 3:40 PM on August 25, 2013

Did you save after deleting the bottom 5 rows? Again, it won't work until after you save.
posted by brainmouse at 3:41 PM on August 25, 2013

ASAP Utilities is an Excel add-in that does all kinds of handy stuff, including deleting empty rows for you.
posted by odinsdream at 3:43 PM on August 25, 2013 [1 favorite]

Did you save after deleting the bottom 5 rows? Again, it won't work until after you save.
Sorry, forgot to save. I tried it again and saved this time but I got the same result as before.
posted by mataboy at 3:48 PM on August 25, 2013

« Older Water quality testing   |   Devices for speech-to-text for child with learning... Newer »
This thread is closed to new comments.