Jumping to the Last Possible Cell

Written by Allen Wyatt (last updated September 3, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


Ulises downloads a lot of workbooks from his company's database. When working with these, if he presses Ctrl+End, Excel always jumps to the last possible cell in the worksheet (XFD1048576), even if there is nothing there. This occurs even when Ulises deletes every row and column from the point of his last known datapoint. He doesn't understand why this happens.

It is hard to track down why this might happen, as the cause could be a number of different things. Assuming that the workbooks are generated by a program other than Excel, you may need to go back to whoever developed those programs and get them to modify the workbook creation process in some way.

The first thing you'll want to do, though, is to check what is being provided in the workbook. If pressing Ctrl+End takes you to XFD1048576, then move one cell to the left and enter the following formula:


If the formula returns a value greater than 0, then there is something in the cell. (It could be one or more spaces, which would be effectively invisible on the screen.) In this case, the only solution is to delete the rows and columns that don't contain meaningful data, such as the columns and rows with nothing but spaces.

If the formula does return 0, then if is very possible that the program that generated the workbook applied formatting to the cells that are empty. How you handle this possibility is described in this Microsoft article:


Regardless of the cause, when you delete rows and columns to reset the last cell position, you'll want to save the workbook and restart Excel in order to have the program recognize the reset last-cell position.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12970) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Finding a Missing Toolbar

A few methods for finding an errant toolbar in Word.

Discover More

Changing Input Conventions

Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. ...

Discover More

Checking Lock Status of Cells

When you first create a worksheet, all the cells in that worksheet are formatted as locked. As you unlock various cells ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Changing the Default Font

It makes sense that when Excel creates a blank workbook, it must figure out which font to use for that workbook. ...

Discover More

Finding the Number of Significant Digits

When looking at a number, you may wonder how many significant digits it contains. The answer is not always an easy one, ...

Discover More

Selecting Multiple Cells by Mistake

Click on a cell and you expect the single cell to be selected. If you instead get a group of cells, it can be frustrating ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2023-12-31 11:14:29

J. Woolley

Re. my comment dated 2022-09-04, I have discovered the expressions for LastNonBlankRow and LastNonBlankCol do not properly account for dynamic array formulas. Replace both instances of LookIn:=xlFormulas with LookIn:=xlValues to correct them.

2022-11-21 10:43:44

J. Woolley

There is an error in my most recent comment below. It should be:

With ActiveSheet
    LastUsedCell = .Cells(LastUsedRow, LastUsedCol).Address
    If LastNonBlankRow <> 0 And LastNonBlankCol <> 0 Then _
        LastNonBlankCell = _
            .Cells(LastNonBlankRow, LastNonBlankCol).Address
End With

2022-09-05 12:07:53

J. Woolley

With the VBA expressions in my most recent comment below, perhaps I should have included these obvious text results:

With ActiveSheet
    LastUsedCell = .Cells(LastUsedRow, LastUsedCol).Address
    If LastNonBlankRow <> 0 Then LastNonBlankCell = _
        .Cells(LastNonBlankRow, LastNonBlankCol).Address
End With

Ctrl+End always jumps to the LastUsedCell. Notice it is possible for all cells to be blank; in this case, LastUsedCell is "$A$1" and LastNonBlankCell is "" (default string).
From my previous comment you might notice the logic for blank columns is especially confusing. My results are for Win 10 Excel 365 (64-bit). The results for previous (or future) versions might vary.
As described, both LastUsedRow and LastUsedCol count formatted cells that are blank. But if you select and format an entire blank row (like $15:$15) or blank column (like $H:$H), this does not imply that all blank cells in that row or column are counted; they are not. The actual result is as if only the first cell in the blank row or column was formatted; i.e., the blank row or column is considered Used, but not all blank cells in that row or column.
As described, LastUsedRow includes blank hidden rows. It is worth noting such rows are not included after unhidden (unlike blank hidden columns).

2022-09-04 13:38:15

J. Woolley

Here are some useful VBA expressions:

With ActiveSheet
    With .UsedRange
        LastUsedRow = .Row - 1 + .Rows.Count
        LastUsedCol = .Column - 1 + .Columns.Count
    End With
    Set R = .Cells.Find(What:="*", After:=.Cells(1), _
        LookAt:=xlPart, LookIn:=xlFormulas, MatchCase:=False, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If R Is Nothing Then LastNonBlankRow = 0 _
        Else LastNonBlankRow = R.Row
    Set R = .Cells.Find(What:="*", After:=.Cells(1), _
        LookAt:=xlPart, LookIn:=xlFormulas, MatchCase:=False, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If R Is Nothing Then LastNonBlankCol = 0 _
        Else LastNonBlankCol = R.Column
End With

A blank cell contains neither a formula nor a constant. (A cell containing a constant apostrophe or space character looks blank but is not.) All Last... expressions count cells (including hidden cells) that contain a formula or a constant.
Both LastUsed... expressions also count formatted cells that are blank. LastUsedRow includes blank resized rows, but LastUsedCol does not include blank resized columns. LastUsedRow includes blank hidden rows. LastUsedCol includes blank hidden columns only if they were resized or adjacent to a resized column or a non-blank or formatted cell before hidden; in this case, they continue to be counted after unhidden until the workbook is closed and reopened. But if they remain hidden when the workbook is closed, they will be counted when the workbook is reopened.
The LastNonBlank... expressions exclude blank cells.
For the final example in my previous comment below, here are the results:
LastUsedRow/Col = 9/4
LastNonBlankRow/Col = 1/4

2022-09-03 17:55:12


I always found it useful to run the following macro when I found the last cell was in an obscure location due to formatting issues. I put it in my autoloading personal macro workbook and use it quite often. If it didn't fix the problem I knew there actually had to be something unseen in cells, like spaces, and had to do a more manual row/column deletion solution.

Sub LastCellSet()

Set LastCell = Cells(Range(Cells(1, 1), ActiveSheet.UsedRange).Rows.Count, Range(Cells(1, 1), ActiveSheet.UsedRange).Columns.Count)
End Sub

2022-09-03 15:23:15

J. Woolley

I'm using Win 10 Excel 365 (64-bit). If I start Excel with a 'New blank workbook', Ctrl+End takes me to cell $A$1 on Sheet1 and ActiveSheet.UsedRange.Address is $A$1 as expected. If I resize column E, $A$1 is still the last used cell. But if I resize row 9, the last used cell becomes $A$9. Microsoft's procedure referenced by the Tip doesn't resolve the issue, nor does saving the file and restarting Excel.
If I enter a value in cell $B$1, the last used cell becomes $B$9. If I enter a formula in cell $C$1, the last used cell becomes $C$9. If I reformat blank cell $D$1 (change the font), the last used cell becomes $D$9.
I conclude the last used row is influenced by resizing.

2022-09-03 10:57:08

Alex Blakenburg

You don't need to restart Excel to have the program recognize the reset last-cell position, you just need to hit Save on the workbook.
Using VBA any command involving the UsedRange property will also do it eg ActiveSheet.UsedRange.

If deleting rows and columns doesn't fix the issue try resetting all the row height beyond the data range to the standard height.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.