Home >  Exploring Excel  > 

Excel 97-2007:

Resetting the Used Range of
A Scroll Box

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