# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Can a vertical scroll bar be reset?

## dawgfan

My spreadsheet has 459 rows; however, the vertical scroll bar ends at row 569738. I have attempted to delete all of the extra rows by holding  SHIFT and CTRL, striking the down arrow then right-clicking on the left margin of highlighted row numbers and choosing delete. I have also done the same procedure except clicking clear all from the editing menu. None of these methods has removed the rows and enlarged the vertical scroll bar to a proportionate size in relation to the number of rows.

Any and all help much appreciated!

----------


## dominicb

Good afternoon dawgfan

All those rows contain something - they may look empty, but may contain formatting.  Delete all these rows, and the vertical scroll bar will be reset *next time you open the workbook*.

HTH

DominicB

----------


## dawgfan

I would have thought so as well; however, I have done it multiple times and the vertical scroll bar continues to be extremely small. 

I am holding SHIFT and CTRL then hitting the down arrow and selecting rows 459 thru 1048576 and clearing all and deleting rows..........

I then save and close. Nothing has changed when re-opened.

----------


## shg

Try CLEARING the rows first, then DELETING, then save, close, and reopen.

----------


## dawgfan

OK, tried clearing then deleting and no different than other methods.

PS - saved, closed and reopened...

----------


## shg

Post the workbook, with everything eliminated that isn't needed to illustrate the problem?

----------


## dawgfan

Unfortunately, it's almost all proprietary so it's take some time to manipulate. Don't give up as I'll try to post something today. Thanks.

----------


## Kafrin

Just to clarify, it won't work if you just hit the Delete key to clear the contents, you must select the rows (entire rows, not just some cells), then right click on the row selectors and choose Delete.  Press Ctrl+Home and then save the worksheet.

I appreciate you may already be doing this, but I thought it was worth making clear.

----------


## dawgfan

> Just to clarify, it won't work if you just hit the Delete key to clear the contents, you must select the rows (entire rows, not just some cells), then right click on the row selectors and choose Delete.  Press Ctrl+Home and then save the worksheet.
> 
> I appreciate you may already be doing this, but I thought it was worth making clear.



Yes, the only thing I did not do as above was Ctrl-Home prior to saving. I just did this and it's the same as always.

I did play with the sheet to the extent that I deleted some of the rows of my real data and the vertical scroll bar grew larger as I deleted additional rows. Does this tell anyone anything important?

----------


## shg

> Does this tell anyone anything important?



It strongly suggests that you've been deleting _contents_ instead of deleting _rows_, as Kafrin made clear is necessary.

----------


## Kafrin

If you're really stuck, you could create a new workbook and only copy across the rows you actually want.  Don't forget to also copy any macro code, named ranges and additional sheets as well.

Bear in mind that you may have some code which is causing the problem - any code that copies the entire sheet instead of just the cells with data will cause the problems you're having.  If you do have any code (macros) running when you save, close or open the file, we made need to look at that.  To adapt this code to only look at relevant cells you need to look at the SpecialCells method using xlLastCell.

----------


## dawgfan

I'm just hosed.

I tried copying only my 458 rows to a new workbook.Then I copied only the cells I am using to a completely new workbook. In both cases the vertical scroll bar shrunk up to nothing again.


You barely touch it and it moves you down several thousand rows!

----------


## Kafrin

OK, try deleting all rows out of a copy of your spreadsheet (again that's delete, not just clear contents).  Then save it, close it and reopen it.  If the scroll bar is still screwy, post this empty workbook here, as it should no longer have proprietary info in it.  If this fixes the problem, at least it's a starting place - as you may have gathered we're a little confused as to why this is so difficult to fix, so any extra tests or info will be useful!

----------


## janabaker

I am sure this person has already figured out her question, but I thought I would add something. 
This has happened to me. Quick fix: choose to view your spreadsheet in PAGEBREAK PREVIEW. Go down to the last solid blue line and drag it up to the bottom of your "active" sheet (last row of typing or formulas). Change your view back to NORMAL and your scroll bar should be reset. If not, close the spreadsheet (saving changes) and re-open. Your scroll bar should be reset.
I know there can be a hundred reasons why your scroll bar has "chosen" to scroll to the bottom of the entire worksheet, so this may not work for you. But it has always worked for me.

Thank you.

----------


## optionzz

Thanks to all who tried to help this person.  I've had this problem on and off, but now have spent hours on a sheet trying to make the vertical scroll bar propostionally conform to the small number of rows that actually have data.  It THINKS I want to scroll to the last row, and ANY move of the elevator thumb (little square in the vertical scroll bar) moves down 1000's of rows, moving me out of the range of my 900 row spreadsheet.

I've done EVERYTHING suggested here, but nothing changes.  I've cleared, deleted, saved, and such over and over.  

This sheet has a table that starts in row 4 and goes for 900 rows.  The top 3 rows have only notes and such, no graphics.  I finally gave up, copied my data the 900 rows by AG columns, and pasted it into a new sheet (in the same workbook).  I then renamed and renumbered the bad one, and named/numbered my new one.  It works as it should (the elevator bar is proportional to the data, i.e., moving it 1/2 way down the screen positions me at the middle of my 900 rows of data).

I've yet to reload the sheet (it takes LOTS of wall clock run  time, as it is data pulled from a directory full of files by a VBA script.  Don't want to do it, but I'm hopeful it won't break the sheet (I have no idea what would cause the elevator bar to get out of synch such that NOTHING I could do fixed it).  I'm posting this in the hopes that someone understands how the vert scroll bar gets its end points and how to reset it to proper proportionality, as this is maybe the 10 time this year with Excel 2010 that this has happened...each time to a dif worksheet in a different workbook.  There's a bug there somewhere!
Thanks!
Pete A

----------


## arlu1201

optionzz,

Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## Capt.Ragnar

Go one row below the last line of data.  Shift + Space bar to select the entire row.  Shift down arrow and select 3 rows.  Clear all data.

Select the middle row of the 3 cleared rows.

Ctrl + Shift + Down Arrow
Ctrl + Shift + Right Arrow

Alt, E, D

F9

Ctrl F4

Reopen and Wah La.  Fixed.

----------


## jimmalk

Just my opinion, but I believe optionzz has a valid point with 



> how the vert scroll bar gets its end points and how to reset it to proper proportionality



 This seems to be a relevent issue related to this problem and adding to the discussion. Not to offend anyone, just my opinion. Best Regards

----------


## jimmalk

Maybe this is a start?



```
Please Login or Register  to view this content.
```

----------


## TK5

Hey guys,

I am having the same problem and can't figure it out.  The data is only in a small section but I can't get the scroll bars to reset.  Clearing, deleting, saving, closing, Googling the problem...  I can't solve it.

For business reasons, I deleted the data but left the formatting and shaded the area that used to contain the data.  Can anyone get the scroll bars back to normal size and eliminate the extra blank rows and columns?

Thank you for your help.

Cheers,TinyScrollBars.xlsxTinyScrollBars.xlsx
Todd

----------


## shg

Select row 404 through the bottom.

Clear > all

Delete > Delete Sheet Rows

Delete all of the comments.

Save, close, and reopen.

----------


## TK5

AWESOME!!  It was the comments that were killing me.

Thank you!

-Todd

----------


## CJMora

I found that if the cell has "Comments' associated to it, these can cause the problem you're seeing with either of the scroll bars.  Excel has some glitch that causes these comments fields to expand, move, and/or shrink.  To find out if you're having this problem, hover your mouse over a cell with comments in it...if the comment box pops open and extends past the bottom/side of the page, this could be the culprit.

----------


## iamquitting

This worked for me




```
Please Login or Register  to view this content.
```


Hope someone finds it useful

----------


## SamYounno

> This worked for me
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



Cheers iamquitting, simple and effective. Works a treat providing you delete all the subsequent blank rows first.

----------


## Buntcake

I just resolved this issue in a workbook in Excel 2010 by cutting and pasting the data into a new tab in the workbook.  Make sure not to cut the entire sheet, only highlight the area where your data lies.

Hope this helps.

----------


## RedBullet100

> Go one row below the last line of data.  Shift + Space bar to select the entire row.  Shift down arrow and select 3 rows.  Clear all data.
> 
> Select the middle row of the 3 cleared rows.
> 
> Ctrl + Shift + Down Arrow
> Ctrl + Shift + Right Arrow
> 
> Alt, E, D
> 
> ...



Fantastic, this worked a charm for me.  Thanks a lot

----------


## PANKAJJAIN

hello everyone, I am using Office 2007 and since last few months my both scroll bar are are low in size I tried everything available on netfor resizing removing the unnecessary rows /column saving
 and step further i have re installed office twice and each time after uninstalling and before re installing cleaned the registry by Ccleaner.
 but everything in vain  

please what else can i do  ?

----------


## Winon

See Post # 21.

Find the last Row with Data you want to keep, then just below that Row, Select all the Rows all the way down to the bottom, and Delete those Rows.

Now find the last Column to the Right with Data you want to keep. In the first Column right of that last Column of Data you want to keep, Select all the Columns to the Right, and Delete it as well.

Save and close the Workbook. Reopen it again and if the problem is not solved, repeat the steps and then save the Workbook under a new name, and check it then.

Hope it helps.

----------


## yamikai

Did anyone figure this one out? I am having the same problem, and have tried all of the suggestions. Using 2013.

----------


## fatodubs

I just solved this problem, thanks in part to shg's mention of comments.

My issue was actually a comment within the cells that I was using, but that had somehow been stretched a few thousand rows below it's actual cell. The image here is a very mild example. Deleting the comment and then re-adding it worked (the comment is needed for a macro).

Thought I would share in case anyone else was running into a similar issue. Perhaps someone else can identify a way to find comments in a document if you don't know where they are.

comment.PNG

----------


## deuteron

Thanks for the clue!  I would never have looked for Comments, but found 3 (2 in hidden columns) that were in excess of 2,000" tall - taking me to row 24,000+.  Repeated VBA, and Delete Row/save attempts didn't work, but this did.  {Inherited workbook - FWIW}

----------


## shg

> Perhaps someone else can identify a way to find comments in a document if you don't know where they are.






```
Please Login or Register  to view this content.
```

----------


## BeBeBe

I had the same problem several times in the same sheet. 
First I could get things right by clearing and deleting all the rows below the used ones but at a certain point this did not help anymore. 
Today I found a solution that works for me.
First I cleared and deleted all the unused rows as I did before. Once this was done I hided all the unused rows and went to the top of the sheet (crtl-Home). 
I saved the sheet and noticed the sheet was about half the size as it was before. Opened the sheet again and the right scroll bar was proportional to the used rows again.
Unhided all rows again and the bar stayed proportional. The sheet is much faster now because of its smaller size. Also pressing CRTL-End leads to the right cel again.
Problem solved (for me anyway).

----------


## sneedy

> Go one row below the last line of data.  Shift + Space bar to select the entire row.  Shift down arrow and select 3 rows.  Clear all data.
> 
> Select the middle row of the 3 cleared rows.
> 
> Ctrl + Shift + Down Arrow
> Ctrl + Shift + Right Arrow
> 
> Alt, E, D
> 
> ...



Nope - didn't work

----------


## alansidman

@sneedy 

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

