Excel 2007 Scroll-bar going to end of sheet

August 25, 2013 3:00 PM

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 (7 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

Response by poster: 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

Response by poster: 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