Reset Excel's UsedRange

If you are frustrated by your scrollbars racing way passed your last row, or they are difficult to control, it is likely Excel thinks it has more data than it actually does and you need to reset it's UsedRange.

All versions of Excel have an 'issue' with resetting the area it believes it is using on any sheet (UsedRange).

This is brought about by say, entering data in columns to the right of the actual data or rows below it.  To test where Excel believes it's furthest row or column is, simply move the scroll bars fully to their right or bottom. Alternatively, you can use the keyboard shortcut Ctrl + End. If this is further than you anticipated, then you need to reset it. (for this example we will pretend the actual data is in A1:Z100, but Excel thinks it has data in A1:AD300)

1. Select the entire rows from 300 to 101 and either, right-click on any selected row label and select Delete or go to Edit>Delete (Home Tab>Cells Group>Delete Button and Delete Sheet Rows.. in 2007). It is important you use delete, not clear or pressing the delete key on the keyboard. Excel appears to do little, but it has actually deleted the rows 101 to 300.

2. Select the entire columns from AA to AD and either, right-click on the selected column label and select Delete or go to Edit>Delete (see above for Excel 2007). Again, it is important you use delete, not clear or pressing the delete key on the keyboard. Excel will delete the columns between AA and AD.

3. Save the workbook. This part is very important, nothing will happen until you have done this. In versions prior to 97 you will also have to close Excel and re-open it.

If you run the scroll bar or Ctrl + End test again, you should see Excel now stops at the actual final row/column and you are back in control :-)