Microsoft KB Archive/244435

= How to reset the last cell in Excel =

Article ID: 244435

Article Last Modified on 5/28/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q244435



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



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. 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 columns
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, type F:IV for columns, or type 5:65536 for rows.
 * 1) Press DELETE.
 * 2) Repeat steps 1 and 2 for the rows that are under the last row that contains data.
 * 3) Save the file.
 * 4) To continue working in the file, close and then reopen the file.

Method 2: Use an Excel add-in
Note The following Excel add-in only applies to English version of Excel, not applies to non-English version of Excel. We recommend that you may change the language in Excel before you use this Excel add-in.

An 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 Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?FamilyId=ECFD076C-B873-48CC-B842-DA999C848C82&displaylang=en

 On the download page, click the Download button. When you are prompted to do so, click Open or Run. When you receive the licensing agreement, click Yes. 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, this location is 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.  Start Excel, and then follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007
 Click the Microsoft Office Button, and then click Excel Options. Click Add-Ins, click Excel Add-ins in the Manage box, and then click Go. If you did not save the file to the Library folder, click the Browse button, locate the file, and then select the file in the Browse window.</li> Click Excess Format Cleaner 1.1, and then click OK.</li></ol>

Microsoft Office Excel 2003 and earlier versions of Excel
<ol style="list-style-type: lower-alpha;"> Click Add-Ins on the Tools menu.</li> If you did not save the file to the Library folder, click the Browse button, locate the file, and then select the file in the Browse window.</li> Click Excess Format Cleaner 1.1, and then click OK.</li></ol> </li> In Excel 2007, click the Add-Ins tab, and then click Clear Excess Formats in XSFormatCleaner.xla in the Menu Commands group.

In Excel 2003 and in earlier versions of Excel, open the workbook that you want to clean up, and then click Clear Excess Formatting in  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.</li> Save, close, and then reopen the file.</li></ol>

Additional query words: large size convert conversion kbtshoot xl97 XL2000 xl2002 xl2003 xl2007 blank xsclean corrupt corruption

Keywords: kbexpertisebeginner kbcode kbdtacode kbhowto kbinfo kbprogramming KB244435

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.