Lastcell, Reset Last Used Cell (Excel 95 only portions)

The following information has been removed from  Lastcell, Reset Last Used Cell   since is is rather unwieldy to expect people to skip down past the Excel 95 material when very few if any people visiting are going to be still using Excel 95.

To find the last cell in use.  This is the convergence of the highest row in use and the highest column in use (or the memory of such). using Ctrl+End

Fix for XL95 and earlier

1. Find last cell using the short cut Ctrl+End
2. Delete unused rows at end of spreadsheet
3. Delete unused columns to right of spreadsheet.
4. The use of a macro can be faster than doing steps 1-3 manually.
5. Save the file.  This must be done with File --> Save.  Exiting the file will not be sufficient.
6. If you are using XL95 you are finished; otherwise, for previous releases of Excel you must Exit Excel.  You should use File --> Exit, since the close button [X] in the upper right corner may not actually close the file (Q176346 might be reason).
7. Reopen the file.  Use Ctrl+End if you wish to verify correct last cell reset.

A modified macro for use on XL95

Sub Reset_LastCell()
   'MS KB Q134617: Resetting the Last Cell Fixes Memory/Printing
   ' Problems Last reviewed: February 3, 1998
   ' Four lines modified later for XL95 per Dana DeLouis 06/07/1998
   ' Six lines for XL97 and up per Dana DeLouis 06/07/98 and 07/24/98
   ' In XL98 use 65536 instead of 16384 represents maximum row address.


   ' For Excel 97 and up activate next line -- ActiveSheet.UsedRange
   'ActiveSheet.UsedRange

   ' 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.
      ' ----- Application.CountA does not exist in XL97 use instead ...
      ' ----- Application.WorksheetFunction.CountA
      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.
         ' ----- Application.CountA does not exist in XL97 use instead ...
         ' ----- Application.WorksheetFunction.CountA
         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
      'next two lines modified and same later per Dana DeLouis 06/07/1998
      .ClearContents
      .ClearFormats
      .Clear
      '.Delete
   End With
   ' Clear and delete the "unused" rows.
   With Rows(lastcell.Row + 1 & ":16384")
      Application.StatusBar = "Deleting Row Range: " & _
         .Address
      'next two lines modified same as earlier per Dana DeLouis 06/07/1998
      .ClearContents
      .ClearFormats
      .Clear
      '.Delete
   End With
   ' Select cell A1.
   Range("a1").Select
   ' Reset the status bar to the Microsoft Excel default.
   Application.StatusBar = False
   ' For Excel 97 and up activate next line -- ActiveSheet.UsedRange
   'ActiveSheet.UsedRange

End Sub

Additional modifications

Capacities and Limits in XL95

Microsoft Excel for Windows 95 specifications

• Application workspace specifications
• Worksheet and workbook specifications
• Charting specifications

Problems fixed in the above macro for XL95 compared to others

• The last referenced cell may be outside area of last used cell
    e.g.  =SUM(a1:a400) may be used for a total but references a400 is well beyond any cell with data.  The macro in the MS KB might change the above formula to =SUM(a1:a47) if no rows have data after row 47.

Problems that still exist using this macro in XL95:

• Loss of shading and other format code outside last used cell
• Loss of cell notes outside last used cell

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved