Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I conceal the unused range from the scroll bar?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I conceal the unused range from the scroll bar?
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 On Aug 18, 11:24*am, Eric Colvin <Eric wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excell 2003 -how to use a scroll bar to adjust the range of a func | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Scroll Area _Named Range in worksheet | Excel Discussion (Misc queries) | |||
Scroll horizontaly with mouse, create same system used to scroll . | New Users to Excel | |||
Scroll Bar Range | Excel Discussion (Misc queries) |