XL: Resetting the Last Cell Fixes Memory/Printing Problems

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a

SUMMARY

Microsoft Excel keeps track of all the cells that you use in a worksheet by using an "activecell" table (also called a "dependency" table). In some cases, the last cell in this table may refer to a cell that is outside of the worksheet area that you are using. When this occurs, random access memory (RAM) or printing problems may result. You may be able to correct these problems by resetting the last cell in the table so that it falls within the area of the sheet that you are actually using.

MORE INFORMATION

This behavior is changed in Excel 97 and Excel 98. When you save the workbook, the last cell pointer is automatically updated to reflect the last cell that contains data.

To reset the last cell address in the versions of Microsoft Excel listed at the beginning of this article, 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 Microsoft Excel to rebuild the "activecell" table.

NOTE: When you use either method, you may receive an "Out of Memory" message as Microsoft Excel attempts to clear and delete the selected range. If you receive this error message, use smaller row or column ranges when you clear data.

Method 1

Use the following steps to reset the last cell by manually deleting excess rows and columns:

1. Select all rows or columns that do not contain data by selecting the appropriate row or column headings.

   TIP: One way to do this is to press F5 and type the appropriate reference (F:IV for columns or 5:16384 for rows).

2. On the Edit menu, click Clear, and then click All.

3. On the Edit menu, click Delete.

4. On the File menu, click Save (click Save As if you want to keep the original file).

Method 2

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

1. Type the following code into a Visual Basic module:

         ' Macro code starts here.
         Sub Reset_LastCell()
   
            ' Save the lastcell and start there.
            Set lastcell = Cells.SpecialCells(xlLastCell)
            ' Set the rowstep and column steps so that it can move toward
            ' cell A1.
            rowstep = -1
            colstep = -1
            ' Loop while it can still move.
            While (rowstep + colstep <> 0) And (lastcell.Address <> "$A$1")
               ' Test to see if the current column has any data in any
               ' cells.
               If Application _
                     .CountA(Range(Cells(1, lastcell.Column), lastcell)) _
                     > 0 Then colstep = 0  'If data then stop the stepping
                  ' Test to see if the current row has any data in any cells.
                  ' If data exists, stop row stepping.
                  If Application _
                        .CountA(Range(Cells(lastcell.Row, 1), lastcell)) _
                        > 0 Then rowstep = 0  
                     ' Move the lastcell pointer to a new location.
                     Set lastcell = lastcell.Offset(rowstep, colstep)
                     ' Update the status bar with the new "actual" last cell
                     ' location.
                     Application.StatusBar = "Lastcell: " & lastcell.Address
            Wend
            ' Clear and delete the "unused" columns.
            With Range(Cells(1, lastcell.Column + 1), "IV16384")
               Application.StatusBar = "Deleting column range: " & _
                  .Address
               .Clear
               .Delete
            End With
            ' Clear and delete the "unused" rows.
            With Rows(lastcell.Row + 1 & ":16384")
               Application.StatusBar = "Deleting Row Range: " & _
                  .Address
               .Clear
               .Delete
            End With
            ' Select cell A1.
            Range("a1").Select
            ' Reset the status bar to the Microsoft Excel default.
            Application.StatusBar = False
         End Sub
   
   

2. Switch to the sheet in which you want to reset the "activecell" table and click Macro on the Tools menu. In the list of macros, click Reset_LastCell, and then click Run.

3. On the File menu, click Save (if you want to keep the original file, click Save As).

REFERENCES

For additional information about resetting the last cell address in earlier versions of Microsoft Excel, see the following article in the Microsoft Knowledge Base:

   ARTICLE ID: Q100406
   TITLE     : Excel: Resetting Last Cell Address Fixes Memory/Printing
               Problems