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
|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() ActiveSheet.UsedRange End SubThis 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.
Microsoft Excel for Windows 95 specifications
[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).RowThe following includes UsedRange and is more direct (Excel 97 and up).LastRow = UsedRange.Rows.CountIf 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).RowThe reasonable equivalent:EndRow = Cells(ActiveSheet.UsedRange.Rows.Count+1, _ ActiveCell.Column).End(xlUp).RowEither 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
The following will clear all constants past row 1 and column 1, leaving formatting unaffected.
- 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.On Error Resume Next Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _ SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents Range("B2:" & Cells.SpecialCells(xlLastCell).Address). _ SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
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
[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
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.
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 SubYou 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.
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;
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
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
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.
Application.ScreenUpdating = False
Application.ScreenUpdating = True
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 have a last cell problem please try to figure out how it happened, avoid doing the following;
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.
Worksheet solution find last text value in a columnFrom 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.=OFFSET(Solo!$C$5,0,0,COUNT(Solo!$C$5:$C$350),4)
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
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
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
- Slow Response, Speeding up Excel, Enhancing Performance.
- 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.
- 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