Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     
 

Home >  Exploring Excel  > 

Excel 97-2007:

Resetting the Used Range of
A Scroll Box


"My scroll box for a particular worksheet is very tiny, but the worksheet only uses rows 1 through 33 and columns A through K.  How can I reset the scroll box so that it is at the bottom of the scroll bar when I am at the bottom of my worksheet, rather than it reaching the bottom only when I reach row 8156 (for example)." -- Matt K.

by Charley Kyd

Matt,

Try pressing Ctrl+End, which takes you to the bottom-right corner of your used range. In your case, I imagine that Excel selects a cell near the bottom of your worksheet. Instead, Excel should select cell K33 in the worksheet you describe.

The two issues are related, because your "used range" has gotten very large. Typically this occurs when you accidentally format or enter data into a cell deep in your spreadsheet. You discover the problem later and press the Delete key, but that doesn't always fix the problem.

What you need to do is to delete the unused rows and columns. Then save your workbook.

Warning: If your workbook is important, it would be a good idea to save this workbook under a different name when you do this. You don't want to accidentally erase important information then save that change.

In current versions of Excel you only need to save your workbook to reset the used range at this point. In earlier versions, you would need to close and then re-open your workbook.

Here's how to delete the rows and columns quickly:

To delete the rows: Go to the bottom row of data and then move down several rows, just to be sure. Press Ctrl+Shift+End, which selects the range from your active cell to the bottom-right cell of your used range. Press Shift+Spacebar to extend this range so that the entire rows of this range are selected. Then press Ctrl+- (Ctrl and your minus key) to delete those rows.

To delete the columns: Go to the right-most column of your data, plus a column or two, and press Ctrl+End. Again, this selects the range from your active cell to the bottom-right cell of your used range. Press Ctrl+Spacebar to extend this range so that the entire columns of this range are selected. Then press Ctrl+- (Ctrl and your minus key) to delete those columns.

In both cases, pay attention to which rows and columns are selected when you press Ctrl+End. If the selection moves up from your selected row or left from your selected column, don't delete that range because you might delete real data.

Charley

 

 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

 

Excel Dashboards

Learn how to create top-quality dashboard reports with Excel.