XL: Resetting the Last Cell Fixes Memory/Printing Problems

ID: Q134617

The information in this article applies to:

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 professionals 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/
Use the following steps to find and delete all the unused cells:

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
Additional query words: 7.00 5.00 5.00a 5.00c large size convert conversion XL5 XL7 kbtshoot 
Keywords          : kbcode kbprg xlloadsave PgmOthr xlprint 
Version           : WINDOWS: 5.0, 5.0c, 7.0, 7.0a; MACINTOSH: 5.0, 5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbinfo

Last Reviewed: May 17, 1999