Lastcell, Reset Last Used Cell

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

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

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

Automatically Resetting the Last Cell in Excel 97, Tip 73, John Walkenbach.  Activesheet.UsedRange has also been suggested.

Excel 97, Excel 2000 and later users please skip down to Reset All Lastcells in the Active Workbook. 
The original Excel 95 material has been  removed.   Some macro coding material snippets will remain but you may
want to skip over them with the above link.

Capacities and Limits in your version of Excel

Microsoft Excel for Windows 95 specifications

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

Some Additional Lastcell related coding

    [skip down to Reset All Lastcells in the Active Workbook]
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 [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 (Excel 97 and up).

  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
   NumberofRowsInSelection = selection(selection.cells.count).row
   NumberofColumnsinSelection = selection(selection.cells.count).column

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

  ' Place the curor on the last cell in Column E
   Cells(Cells.Rows.Count,"E").End(xlUp).Select

  'Place the cursor on the cell below the last entry in column A 
   cells(rows.count,1).End(xlup)(2).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

   'row of first and last cell in a selection (or range i.e. rng)
   FirstRow = selection(1).row
   LastRow = selection(selection.Count).Row

 
   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

Coding for Range that might be included (#range)

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

Selects rectangular range that contains all the cells in a worksheet that have ever contained data or formulas.

The following will clear all constants past row 1 and column 1, leaving formatting unaffected.

  On Error Resume Next
  Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _
     SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
  Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _
     SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents 

Some Sheet related coding (#sheets)

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

Some Worksheet Function coding for lastcell in column (#formulas)

 [skip down to Reset All Lastcells in the Active Workbook] Row number:
  =SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))
Row number of the last numerical value within column C...
  =MATCH(9.99999999999999E+307,C:C)

Last Value in Column
  =INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))
Last Numeric value
  =LOOKUP(9.99999999999999E+307,A:A)
Last Text value
  =MATCH(REPT("z",255),A:A)

Last Cell in Column (#lastincolumn)

If you don't know if its numeric or text, try
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1:100)

which is an array formula, so enter with CTRL-SHIFT-ENTER

If you know it is numeric, this non-array formula works
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

use this for text
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

Also see Last Value In Range, location of the last filled element within a range (either in a column or a row), Frank Kabel and Bob Phillips (based on Frank's posting 2004-05-31)

The worksheet solutions above will work faster than a programming solution, but here is a comparable User Defined solution that may be of interest.

Function LastInColumn(rng As Range) As Variant
   LastInColumn = Cells(Rows.Count, rng(1).Column).End(xlUp).Value
End Function

=LastInColumn(A:A)
=personal.xls!LastInColumn(A:A)

The reason to use a range as the argument instead of a number for the column number is to avoid having to use Volatile.  By using a range, any change in the range will recalculate the value in the cell.  Am using only the leftmost column of that range.  If you specify  A1:B23,  then only column 1 will be used, but you may fail to get an immediate recalculation if the last cell (highest row number) in column A is beyond row 23. The function would still get recalculated eventually, but not immediately.

Also see macro and toolbar icons to get to top of a column, or the last cell in a column.

Lastcell Involvement with Merged Cells (#mergedcells)

 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True
   Dim ma As Range
   Cells.Font.ColorIndex = 0
   Set ma = Range(ActiveCell.Address).MergeArea
   MsgBox ma.Address & " -- " & ma.Rows.Count & " -- " & ma.Columns.Count _
     & " ---- " _
     & ActiveCell.Row + ma.Rows.Count - 1 & ", " _
     & ActiveCell.Column + ma.Columns.Count - 1 _
     & " -- " _
     & Cells(ActiveCell.Row + ma.Rows.Count - 1, _
        ActiveCell.Column + ma.Columns.Count - 1).Address
     
   If ActiveCell.MergeCells Then
      ActiveCell.Font.ColorIndex = 3
      ma.Cells(1, 1) = ma.Address
   End If
  
  MsgBox ActiveCell.Row - 1 + ActiveCell.MergeArea.Rows.Count _
    & " " & ActiveCell.Column - 1 + ActiveCell.MergeArea.Columns.Count
    
 End Sub

     nr = Selection.Rows.Count
    nC = Selection.Columns.Count
    
    Set lastcell = Cells.SpecialCells(xlLastCell)
    
    If nr > lastcell.Row Then nr = lastcell.Row
    If nC > lastcell.Column Then nC = lastcell.Column
    'check if lastcell is a merged cell in which case add
    '....Correction for nr and nC if it is a merged cell 2006-07-05
    ir = Cells(nr, nC).Row - 1 + Cells(nr, nC).MergeArea.Rows.Count
    ic = Cells(nr, nC).Column - 1 + Cells(nr, nC).MergeArea.Columns.Count
    nr = ir
    nC = ic

Reset All Lastcells in the Active Workbook (#resetall) ««

The following code is non destructive and may clear up some lastcell problems.  It was tested on a workbook that had some lastcell problems artificially generated by inserting rows and columns and deleting the same inserted rows and columns.  (Excel 97 and up)
 

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  Install  page, or a simpler page Getting Started with Macros (getstarted.htm).

Sub Reset_lastcell()
  'David McRitchie,  http://www.mvps.org/dmcritchie/excel/lastcell.htm
   Dim x As Long  'Attempt to fix the lastcell on the current worksheet
   x = ActiveWorksheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
End Sub

Sub Reset_all_lastcells()
  'David McRitchie,  http://www.mvps.org/dmcritchie/excel/lastcell.htm
   Dim sh As Worksheet, x As Long 
   For Each sh In ActiveWorkbook.Worksheets
     x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
   Next sh
End Sub

You can check results before and after by checking File, Properties, size

While the above is very fast, it doesn't always reduce the size of worksheets that could have been reduced so here are two more pages.

• Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish.  The basis of deletions is all columns without content, and all rows without content.  Warning:  merged cells problems -- a merged cell has content only in the upper left cell of a merged cell group.  You could clip off unused parts of merged cells and cells formatted as merged as in a form.  It is important that you format entire columns because formatting in partially formatted columns will be lost from the deletion points.
• 244435 - How to reset the last cell in Excel [ref], Microsoft KB article, Microsoft Excel keeps track of all the cells that you use in a worksheet by using an active cell table (also named a "dependency" table). Sometimes, the last cell in this table may refer to a cell that is outside the worksheet area that you are using.  I doubt that formatting an entire worksheet at once is the problem.  There is an addin that you have to agree to a licensing agreement, that probably does the same as macros you find in the MS KB, on websites, and newsgroups.

There is also an a similar macro named CleanUpLastCells in the companion code for this webpags.

Making the activecell the LastCell (#MakeLastCell)««

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 beyond 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.

If the print area extends beyond the Last Cell (Ctrl+End) then the problem is not a last cell problem but probably either the Print Area (File menu) has been set beyond the last cell, or a cell is printing beyond the border of the page and not seen because the characters are spaces or non-breaking spaces (see TrimALL macro.
 

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  Install  page, or a simpler page Getting Started with Macros (getstarted.htm).

Sub makelastcell()
  'David McRitchie,  http://www.mvps.org/dmcritchie/excel/lastcell.htm
  Dim x As Long     'revised 2001-08-09 to remove false indication
  Dim str As String    'revised 2006-07-05 for lastcell to be is a merged cell
  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 + ActiveCell.MergeArea.Rows.Count & ":" & Cells.Rows.Count).Delete
  Range(Cells(1, ActiveCell.Column + ActiveCell.MergeArea.Columns.Count), _
     Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
  xLong = ActiveSheet.UsedRange.Rows.Count   'see J-Walkenbach tip 73
  xLong = ActiveSheet.UsedRange.Columns.Count 'might also help

  Beep
  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, " _
     & "possible merged cells involved, check your results"
End Sub

The macro above assumes that you will SAVE later on; otherwise, your changes will not be permanent.

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 macro Reset_all_lastcells in topic Reset All Lastcells in the Active Workbook (#resetall)

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 Long
  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

   Dim xlong As Long
   '-- Eliminate rows after last use in Column A
   Range(Cells(Rows.Count, 1).End(xlUp).Offset(1), _
      Cells(Rows.Count, 1)).EntireRow.Delete
   '-- to fix last used row indication in  Ctrl+End,
   '--  may still require a Save
   xlong = ActiveSheet.UsedRange.Rows.Count

Some Things That Waste Space in Spreadsheets (#waste)

• 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 (journaling?) option in OUTLOOK as it logs what you do -- turn it off.
• Additional topics including formatting and styles are mentioned on slowresp.htm#bloated.

Tools (#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.
• See the formulas by switching to the formula view or use special cells to identify formulas.
• 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.

• Watch how you are saving files, if you are saving for Excel 95 and Excel 97 through 2002 you are doubling the size of file.  If you are not exchanging files with someone with Excel 95, they you are wasting time and space by storing file in this manner.
• 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 journaling OFF.  Journaling 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.

• 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)

Off Topic

Worksheet solution find last text value in a column

From a Tom Ogilvy post 2002-06-23 in misc.

=Index($A$1:$A$500,Max(if(istext($A$1:$A$500),Row($A$1:$A$500))),1)

Entered with Ctrl+Shift+Enter rather than just enter display the last text value in column A.  Extend the range if you will have more than 500 rows.

For some comparable macro solutions see toolbars macros from Tom Ogilvy.

Identifying the last cell in a range (#bottom)

Also see Lastcell page.  The following are all in one thread 2004-01-15.

 
  =OFFSET(Solo!$C$5,0,0,COUNT(Solo!$C$5:$C$350),4)

  range("Solo")(range("Solo").Rows.count,range("Solo").Columns.Count).select

Select the bottom right cell of the named range "solo".

Sub testme()  'Dave Paterson, misc,
    Dim myRng As Range
    Set myRng = Worksheets("solo").Range("test1")
    With myRng
        .parent.select
        .Cells(.Cells.Count).Select
    End With
End Sub

Counting, what are you measuring (#counting)

A  1    2    3    4  20   5  79   6  39   7    8  48   9  75  10  60  11  17  12  38  13  58  14

Preparation of data shown at left: The data at the left was created with a macro to fill in values for cells A4:A12, a row was inserted before row 10 and then that row was deleted the last cell remains at the high water mark of A14. Some worksheet formulas:    results and formula   9   =COUNTA(A:A) Some VBA coding: ActiveSheet.Cells.SpecialCells(xlLastCell).row   returns 14 ActiveSheet.UsedRange.Rows.Count returns 10 ActiveSheet.Cells.SpecialCells(xlLastCell).row   returns 13, after correction from rows.count Application.CountA(Range("A:a"))   returns 9 ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select ActiveSheet.UsedRange.Address   returns the string $A$4:$A$13 and corrects the lastcell location Note Rows.count does not include rows 1-3, but does include row 7 Locate the next cell after the last cell in column A with content -- see toolbars.htm for similar code '-- for this example cell A14 would be selected since A13 has content

 

A B C D  1          2          3    B3  C3  D3  4    B4  C4  D4

ActiveSheet.UsedRange.Rows.Count is 2 Cells.SpecialCells(xlLastCell).Row is 4 ActiveSheet.UsedRange.Address is $B$3:$D$4

Related Items (#related)

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 --]

How to find the last ROW with any data, Tom Ogilvy, collection, 2002-11-06.

Excel Oddities: How big is a worksheet?, John Walkenbach, answers a question I'm sure you wanted to know:

How long would it take to fill up a worksheet at 1 cell per second -- if you actually could store it.

Find out how big the sheets really are by separating them into a temporary file of one sheet per workbook.

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

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

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