Lastcell, Reset Last Used Cell

« Code for Reset_all_lastcellsMakeLastCell, and QueryLastCells in: «
Home page:
[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  Install  page, or a simpler page Getting Started with Macros (getstarted.htm).

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()
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.  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

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

Microsoft Excel for Windows 95 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
  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, _
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

  'Place the cursor on the cell below the last entry in column A 

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

   Cells(Cells.Rows.Count, "B").End(xlUp).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)

   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)

Clears formulas and values within a range
Clears formats within range
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)  ].
Clears notes and sound notes within a range
Clears groups involved with outlining a range of cells
Deletes a range of cells
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
'Rename current Sheet
    ActiveSheet.Name = "Renamed14a"
'Select a Sheet, actually may be current sheet
'Rename a Sheet, actually may be current sheet
    'Sheets("Sheet14").Name = "Renamed14"
'Select an Existing Sheet
End Sub

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

 [skip down to Reset All Lastcells in the Active Workbook]
Row number:
Row number of the last numerical value within column C...

Last Value in Column
Last Numeric value
Last Text value

Last Cell in Column (#lastincolumn)

If you don't know if its numeric or text, try

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

If you know it is numeric, this non-array formula works

use this for text
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

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)

 1 A1  B1 C1  D1 E1 
 2 A2  B2 C2  D2 E2 
 3 A3  B3 C3  D3 E3 
 4 A4  B4 C4  D4 E4 
 5 A5  B5 C5  D5 E5 
 6 A6  B6 C6  D6 
 7 A7  B7 C7 
 8       x
Problem if the lastcell of the sheet (Ctrl+End) involves a merged cell in the selection.
The problem caused by lastcell is identified at the right.  Consider the marked cells A1:E7 if that were all that was on the sheet the lastcell would be E7, but if D6:E7 were merged then the address of the lastcell (ctrl+End) would be identified by the address of the upper left corner i.e. D6 and Xl2HTML (which has been corrected) would only show Cells A1:D6.  But if you make sure that the last cell is beyond your selected range by adding “x&rdquo to F8 then F8 becomes the lastcell.  Suggest you leve the x in that cell, but you actually did delete the last cell Excel would not notice and would still show F8 as the last cell unless you take extra steps to correct the lastcell.
Concept code
 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

Correction code for Lastcell:
     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,
   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,
   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.

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

  rlong = Cells.SpecialCells(xlLastCell).Row
  clong = Cells.SpecialCells(xlLastCell).Column
  If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
  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
  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: " &  _
  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)

Tools (#tools)

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.

Solutions for some things that consume time in Excel

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.

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;

Off Topic

Worksheet solution find last text value in a column

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


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.


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
    End With
End Sub

Counting, what are you measuring (#counting)

 4 20 
 5 79 
 6 39 
 8 48 
 9 75 
10 60 
11 17 
12 38 
13 58 
    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

 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
Slow Response, Speeding up Excel, Enhancing Performance.

Related Information on Other Sites

Determining the Real Last Cell [-- --], for Excel 95
Automatically Resetting the Last Cell in Excel 97 [-- --]
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

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 send email comments

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