How do I conceal the unused range from the scroll bar?

[Eric Colvin]

I have a fairly large worksheet containing a database of prospective recruits
(6500 rows).

Whereas the columns only scroll as far as the final column (with a couple of
blanks to the right), when I pull the row scrollbar down, it scrolls way past
the bottom of my used range, as far as row 15500 or so - even though the
bottom 900 rows are entirely blank.

Traditionally, if I've had this problem with an Excel sheet, I've overcome
it by simply deleting all the blank rows and saving the sheet. However, this
doesn't work here for some reason; even when I reopen the sheet, the scroll
box continues to behave as though my spreadsheet is really 15,500 rows deep.

It's a small, but irritating problem for whoever's using the spreadsheet.

I've checked the defined names, and they are all either for entire columns,
or for individual cells and ranges well within my used range.

What could be going on?

Is there any way to tell Excel to display only the 6000 odd rows that are
currently in use ?

I have tried setting the ScrollArea using vba, but then the scroll box
simply freezes half way down the scrollbar, and the user cannot get further
down the database to enter data into new rows. Ideally, I would like it to
scroll all the way to the bottom of the bar, and for that to be the bottom
row of my used range (or maybe two or three rows lower) - but for the return
and arrow keys still to take them lower when needed - as with all the other
Excel spreadsheets we're accustomed to.

 

[Pete_UK]

What happens when you do CTRL-End? This should take you to the row
that Excel thinks is the last used row, and if it is way beyond where
you think it should be then you have not deleted those empty rows.

One thing you might also think about is just to hide the rows beyond
6500.

Hope this helps.

Pete