Knowledge Base

Resetting the Last Cell Fixes Memory and Printing Problems

PSS ID Number: 134617

Article Last Modified on 8/19/2003

The information in this article applies to:

This article was previously published under Q134617

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 a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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:

100406 Excel: Resetting Last Cell Fixes Memory and Printing Problems


Additional query words: 5.00a 5.00c large size convert conversion XL5 XL7 kbtshoot XL

Keywords: kbcode kbinfo kbProgramming KB134617
Technology: kbExcel500aMac kbExcel500c kbExcel95a kbExcel95Search kbExcelMacsearch kbExcelSearch kbExcelWinSearch kbHWMAC kbOSMAC kbVBASearch kbZNotKeyword3 kbZNotKeyword6


Send feedback to Microsoft

© 2004 Microsoft Corporation. All rights reserved.