Tuesday, February 14, 2006

Resetting the last cell in Excel

Previously I have written about Identifying the last cell in Excel.

The microsoft website has an excellent article about how to reset the last cell.

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:
Microsoft Downloads - excel addin
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.


Posted by: Jethro | 11:53 PM |