Reset Last Used Cell

Location: http://www.mvps.org/dmcritchie/excel/lastcell.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

If you place some data in a remote location of your spreadsheet and later delete that data Excel retains a memory of the last cell in use even though it is no longer in use.

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

Steps to fix the last cell reference

Though this page is mainly oriented to XL95, it appears my LastCell problems XL95 have been fixed or almost eliminated with maintenance. Excel XL95 comes with WinNT 4.0 and with SP4 applied, I believe I no longer have any lastcell problems.

Fix for XL97 and up (XL97, XL98, XL2000)

This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my GetFormula page.

For XL97 and up you can probably simply use the macro supplied in Q163263. Since the fix for XL97 and up is simpler than for XL95 so I'm putting it here first.

Sub Reset_Range()
      ActiveSheet.UsedRange
End Sub

This will reset the lastcell. If you have formats etc after the lastcell and want to clear them out use the full macro listed for XL95 below and include the line in the above macro as the last line in the larger XL95 one. In any case with XL97 and up you do not need to do a File Save mentioned in Q134617 as long as you include the UsedRange. That's it. You will probably want both macros so you can choose which you want to use.

Automatically Resetting the Last Cell in Excel 97, Tip 73, John Walkenbach.

Excel 97 and Excel 2000 users please skip down to Making the activecell the LastCell.
The next few topic applies to Excel 95 only. Some of the descriptive information may be of interest, but Reset_LastCell() macro is of interest only if you are using Excel 95.

Fix for XL95 and earlier

For XL95 according to Q134617 and Q100406 one must delete unused rows and columns, and save but no need to exit. I have been doing a exit but it probably is not needed.

Find last cell using the short cut Ctrl+End
Delete unused rows at end of spreadsheet
Delete unused columns to right of spreadsheet.
The use of a macro can be faster than doing steps 1-3 manually.
Save the file. This must be done with File --> Save. Exiting the file will not be sufficient.
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).
Reopen the file. Use Ctrl+End if you wish to verify correct last cell reset.

A modified macro for use on XL95

The following VBA macro has been modified to prevent changes in formulas involving references to cells outside the last used cell.

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

To preserve format shading and formulas that apply to an entire column and to an entire row you could change the IV16384 to IU16383 and 16384 to 16383 in the above macro (XL95). This will retain the very last column and the very last row; but, don't do it because the macro will take a very long time to complete as the .Delete must be used. The number of rows is probably higher in XL97. With or without .Delete the macro will run fast if the full range IV16384 and 16384 is used (for XL95, note there are more rows in XL97).

range.ClearContents: Clears formulas and values within a range
range.ClearFormats: Clears formats within range
range.Clear: Clears formulas, values and formats within a range. Removed from macro so as not to impact cells referred to by formulas within the range of cells with formulas and values [i.e. =SUM(h2:h87) ].
range.ClearNotes: Clears notes and sound notes within a range
range.ClearOutline: Clears groups involved with outlining a range of cells
range.Delete: Deletes a range of cells
ActiveSheet.UsedRange.Select: Returns rectangular range that contains all the cells in a worksheet that have ever contained data or formulas.

Capacities and Limits in XL95

HELP --> answer Wizard --> length and columns and rows --> Tell Me About ...

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

Some Additional Lastcell related coding

The LastCell is at the intersection of the last used row and the last used column. If you add or delete cell content or delete or insert rows Excel will remember what you had as the last cell and will retain that until you Save the file, and in the case of Excel 95 that you specifically Close the file with File,Close and the the [x]. This may not be all that reliable as there are exceptions, but this gets you what Excel thinks is the last row. Does not do well when rows were inserted and deleted, for instance.
Prior use of ActiveSheet.UsedRange would provide a more reliable value as Excel will usually then recalculate the lastcell.
  DIM LastRow as Long
  ActiveSheet.UsedRange
  LastRow = Cells.SpecialCells(xlLastCell).Row
The following includes UsedRange and is more direct.
  LastRow = UsedRange.Rows.Count
If you want to base your last row on a particular column to avoid some of the problems involving Excel's memory of last row. The following method is frequently suggested by Tom Ogilvy.
   LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row
The reasonable equivalent:
  EndRow = Cells(ActiveSheet.UsedRange.Rows.Count+1, _
      ActiveCell.Column).End(xlUp).Row
Either of the last two will fail in some manner if you actually use the very last row. (row 65,536 in Excel 2000)
More snippets of code:
   LastRow = Cells.SpecialCells(xlLastCell).Row

   similar but find the last used cell in a column
   LastRow = Range("A1").End(xlDown).Row

   Cells(Cells.Rows.Count,"E").End(xlUp).Select

   Cells(Cells.Rows.Count, "B").End(xlUp).Select
   Selection.EntireRow.Select

   Cells(Cells.Rows.Count, "B").End(xlUp).EntireRow.Select

   Cells.SpecialCells(xlLastCell).EntireRow.Select

   Dim lastcell As Range
   Set lastcell = Cells.SpecialCells(xlLastCell)
   lastcell.EntireRow.Select

   Sub UsedRowsCount()
     'number of rows in another sheet
     Dim mySheet As Worksheet
     Set mySheet = Sheets("Data")
     MsgBox "Used Rows = " & mySheet.usedrange.Rows.Count
   End Sub

Some Sheet related coding

Sub Macro29()
'Create New Sheet
    Sheets.Add
'Rename current Sheet
    ActiveSheet.Name = "Renamed14a"
'Select a Sheet, actually may be current sheet
    'Sheets("Sheet14").Select
'Rename a Sheet, actually may be current sheet
    'Sheets("Sheet14").Name = "Renamed14"
'Select an Existing Sheet
    'Sheets("Map").Select
End Sub

Making the activecell the LastCell

Notes: This macro is set up to delete all rows and columns past the active cell, not just the rows and columns past the active cell but limited to the used areas. This appears to be the only way to guarantee the active cell becomes the last cell.
Color formatting that commences outside the new used area will be lost. Color formatting that starts within the new used area will extend beyound the used area.
All number formatting (includes characters) outside of the new used area will be destroyed no matter the point of origin.
If the resulting last cell remains beyond the active cell, it will be noted as an error. If the active cell is beyond the resulting last cell that is acceptable, and no data should have been removed before the active cell.

This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my GetFormula page.
Sub makelastcell()
Sub makelastcell()
  'David McRitchie,  http://www.mvps.org/dmcritchie/excel/lastcell.htm
  Dim x As Integer
  Dim str As String
  Dim xlong As Long, clong As Long, rlong As Long
  On Error GoTo 0
  x = MsgBox("Do you want the activecell to become " & _
      "the lastcell" & Chr(10) & Chr(10) & _
      "Press OK to Eliminate all cells beyond " _
      & ActiveCell.Address(0, 0) & Chr(10) & _
      "Press CANCEL to leave sheet as it is", _
      vbOKCancel + vbCritical + vbDefaultButton2)
  If x = vbCancel Then Exit Sub
  str = ActiveCell.Address
  Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
  xlong = ActiveSheet.UsedRange.Rows.Count   'see J-Walkenbach tip 73
  'use of filters can interfer with column elimination
  Range(Cells(1, ActiveCell.Column + 1), _
     Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
  Beep 
  xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
  rlong = Cells.SpecialCells(xlLastCell).Row
  clong = Cells.SpecialCells(xlLastCell).Column
  If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
  ActiveWorkbook.Save
  xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
  rlong = Cells.SpecialCells(xlLastCell).Row
  clong = Cells.SpecialCells(xlLastCell).Column
  If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
  MsgBox "Sorry, Have failed to make " & str & " your last cell"
End Sub
If your activecell is outside of the used range, a failure to make your activecell be your last cell will be noted but the new last cell would be consistent with remaining in your current used range limited if necessary by the activecell as the last cell. For example if the usedrange is A1:H55 and you chose J34 as the activecell then the MakeLastCell macro would make H34 become the lastused cell. (Now accepting position of lastcell before active cell)
Test sequence for MakeLastCell: All cells yellow tint, A1:c18 marked, b9:c18 green tint, D:D green tint, E18 "x", E15:J15 green tint, select cell c18 and run MakeLastCell. [all coloring is pale tinted interior colors]
Also see Q123269 -- Large File Size After Saving WK4 File as Excel Workbook

QueryLastCells - Identify Large Sheets

If you want to quickly identify large sheets in workbook for their enormous block of cells in the used range see the BuildTOC macro. Or run a macro such as QueryLastCells (below), if that is all the information you want.

Sub QueryLastCells()
  '2001-03-25 based on BuildTOC, David McRitchie, programming
  ' http://www.mvps.org/dmcritchie/excel/lastcell.htm#QueryLastCells
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Dim cRow As Long, cCol As Long, cSht As Integer
  Dim lastcell
  Dim Testcnt As Long
  Dim BigString As String
  BigString = ""
  Testcnt = 5000
  Testcnt = InputBox("Supply Threshhold for used cells count",  _
         "QueryLastCells", Testcnt)
  If Testcnt = 0 Then GoTo AbortCode
  For cSht = 1 To ActiveWorkbook.Worksheets.Count
    Set lastcell = Worksheets(cSht).Cells.SpecialCells(xlLastCell)
    If lastcell.Column * lastcell.Row > Testcnt Then
       BigString = BigString & Chr(10) & _
         Format(lastcell.Column * lastcell.Row, "##,###,##0") & Chr(9) & _
           " " & Worksheets(cSht).Name
    End If
  Next cSht
  MsgBox BigString
  MsgBox BigString & chr(10) & "Worksheets checked: " &  _
            activeworkbook.Worksheets.count
AbortCode:
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
End Sub

Pieces of code

Delete rows after last cell used in column A

  Range("A1").Select        'Tom Ogilvy 2000-12-18 Programming
  Selection.End(xlDown).Select  'Last used cell in Column A
  Range(Selection.Offset(1,0),Cells(rows.count,1)).EntireRow.Delete

Some Things That Waste Space in Spreadsheets

Last Cell Used is incorrect, which we just covered.
Cells that have a space in them look empty but are not blank (ISBLANK) so affect last cell used.
Cells that have formulas are never blank. (see ISBLANK in Help).
In XL97 saving things in both XL97 and XL95 internally takes twice as much storage as simply saving as XL97.
Formatting will not affect the last cell indication, but will affect the size of a spreadsheet. It is best to format entire rows and columns than to format cells individually. Individual cell formatting can be very costly.
Having an excessive number of spreadsheets that are empty. I have mine set to 3 in my profile BOOK1.xls, set in Tools --> options --> General.
Related to wasted space is wasted time. Users of XL97 may experience excessive time delays due to the Journaling (journalling?) option in OUTLOOK as it logs what you do -- turn it off.

Tools

Locating the last cell, Ctrl+End
Determine which cells are blank and which are not. To identify the blank cells: Select all cells with Ctrl+a or select an area of your choice. To identify the blank cells within the selection area use Edit, GoTo(Ctrl+g), Special Cells button, blanks. Cells that are outside the used area identified by last cell (Ctrl+End) will not be included in the resulting selection.
Code Cleaner, VBA -- Rob Bovey. Cleaning a project involves saving all of its VBComponents out to text files, deleting the components, then importing the components back from the text files. Versions for XL95 and XL97/2000. Stripping comments is optional in XL97/2000 version.
Documentation and Reports see my BuildTOC page and it's related area.

Additional items that consume space

The items previously listed were things that can be avoided. The following items are things that consume space, are not related to last cell, are not necessarily bad but are part of the design considerations. More information can be found on my page Slow Response, Speeding up Excel, Enhancing Performance for additional information.

Shapes, objects, forms, hyperlink objects.

Solutions for some things that consume time in Excel

Office 97. MS Outlook monitoring XL files, etc. Switch XL file journalling OFF. Journalling keeps a log of every file you save in word or excel.
Recalculations, reformatting print items in SETUP.

Application.ScreenUpdating = False
Application.ScreenUpdating = True
See my page Slow Response, Speeding up Excel, Enhancing Performance for additional information.

What Causes Last Cell Problems, or how to avoid them

If you scroll down using the rectangular scroll bar you should only go down as far as your data, if you continue down to row 65,536 in XL2000 and XL97 or 16,384 in XL95 then you have a last cell problem -- or have real data you didn't know you had. The actual number of rows for your version of Excel can be found in the Specification Limits -- see Help in Excel.
If you scroll down in with the small triangles on the scroll bar or holding the cursor down to cause the rows to scroll down you will see 16,384 rows in XL95. In fact holding the cursor down using the fill-handles is very likely what caused a problem in the first place. Check your last cell which is the intersection of last used row and last used col.
Ctrl+End
Try File --> Save and then Ctrl+End again, if not cleared up try the macro.

If you have a last cell problem please try to figure out how it happened, avoid doing the following;

Copying formulas or formatting down to the bottom of the spreadsheet using the fill handles. Formatting an entire column is okay though there is but one format but don't format individual cells by using fill handles down to the bottom.
Entering data into a cell and removing contents with spaces. The cell does not qualify for ISBLANK. To properly clear use Edit -- Delete The Del Key will also work if there was no formatting. or use Edit --> Clear --> (more choices)

Related Items

Slow Response, Speeding up Excel, Enhancing Performance.

Related Information on Other Sites

Determining the Real Last Cell [-- http://www.j-walk.com/ss/excel/tips/tip13.htm --], for Excel 95
Automatically Resetting the Last Cell in Excel 97 [-- www.j-walk.com/ss/excel/tips/tip73.htm --]

Microsoft Knowledge Data Base (MS KB)

Q163263 XL97: New UsedRange Property Behavior
Q134617 XL: Resetting the Last Cell Fixes Memory/Printing Problems (Feb 3, 1998)
excerpt from this page: 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 ...
Q100406 Excel: Resetting Last Cell Address Fixes Memory/Printing Probs (Sept 13, 1996)

Q132439 XL: Last Cell Detected Incorrectly After Change Row Height
Q141692 XL: Visual Basic Example to Retrieve Value from Last Cell
Q176346 XL: Workbook May Not Be Closed When You Click Close Button
Q123269 Large File Size After Saving WK4 File as Excel Workbook

This page was introduced on July 12, 1998.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com