Article ID: 244435 - View products that this article applies to.
This article was previously published under Q244435
Microsoft Excel saves only the part of each worksheet that is in use, meaning the section that contains data or formatting. Sometimes the last cell of a worksheet may be beyond the range of your actual used data. This issue may cause you to have a larger file size than neccesary, you may print extra pages, you may receive "Out of Memory" error messages, or you may experience other unusual behavior. Clearing the excess rows and columns to reset the last cell can help to resolve these issues.
Note You can locate the last cell of the active worksheet by pressing CTRL+SHIFT+END.
The most common cause of the last cell being set outside the worksheet range that is currently in use is excessive formatting. When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use.
This issue may also occur when you import a Lotus 1-2-3 file into Excel. When you save the Lotus file to the Excel Workbook format, Excel cannot determine the last cell in the Lotus 1-2-3 worksheet. Therefore, it makes the whole worksheet active. To reset the last cell address, you can use either of the following methods to clear the unnecessary information (data and formatting) from the unused columns and rows of the worksheet. After you remove the extraneous information, save the sheet to force Excel to rebuild the active cell table.
Note When you use either of the following methods, you may receive an "Out of Memory" error message or a similar error message. This is because Excel tries to delete the selected range. If you receive this error message, use smaller row or column ranges when you clear data.
Method 1: Manually delete excess rows and columnsTo reset the last cell by manually deleting excess rows and columns, follow these steps:
Method 2: Use an Excel add-inAn example Excel add-in that removes excess formatting and resets the last cell is now available for download. To download this add-in, visit the following Web site:
XSFormatCleaner.xlaUse the Downloads section of the web page to download XSFormatCleaner.xla and save it to your PC. Once you have downloaded the xla you can use the following instructions to run the code in the AddIn to remove excess formatting in your workbook.
Article ID: 244435 - Last Review: November 20, 2012 - Revision: 9.0