How to reset the last cell in Excel (244435)

MORE INFORMATION

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 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

To reset the last cell by manually deleting excess rows and columns, follow these steps:

1. Select all columns to the right of the last column that contains data by selecting the appropriate column headings.
   
   TIP: One way to do this is to press F5 and type the appropriate reference, for example F:IV for columns or 5:65536 for rows.
2. On the Edit menu, click Delete.
3. Repeat steps 1 and 2 for the rows that are under the last row that contains data.
4. On the File menu, click Save (click Save As if you want to keep the original file).
5. To continue working in the file, close, and then reopen the file.

Method 2

An Excel add-in to remove excess formatting and reset the last cell is now available for download. To download this add-in, visit the following Microsoft Web site:

1. On the download page, click the Download button. When you are prompted to do so, click Open.
2. When you receive the licensing agreement, click Yes.
3. When you receive the WinZip Self-Extractor prompt, click the Browse button, and then select the location where you want the add-in to be extracted.
   
   Note You can put the file in the Excel add-in location, where it will automatically show up in the Addins dialog box in Excel. That location is the Library folder in the Office install location, typically the C:\Program Files\Microsoft Office\Office\Library folder.
   
   After you select the download location that you want in the Browse dialog box, click UnZip in the WinZip dialog box. Click OK on the successful download prompt, and then click Close in the WinZip dialog box.
4. Start Excel.
   
   On the Tools menu, select Add-Ins. If you did not save the file to the Library folder, click the Browse button, locate the file, and then select it in the browse window. Click Excess Format Cleaner 1.1, and then click OK.
5. Open the workbook that you want to clean up, and then click Clear Excess Formatting in workbook on the File menu. The macro will then clear excess formatting from all worksheets in the file. When the macro is completed, you will be informed that you must save the workbook for the changes to be permanent.
6. Save, close, and then reopen the file.