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


6

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:

=LEN(XFD1048576)

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:

https://support.microsoft.com/en-us/office/locate-and-reset-the-last-cell-on-a-worksheet-c9e468a8-0fc3-4f69-8038-b3c1d86e99e9

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

MORE FROM ALLEN

Protecting Worksheets from Deletion

If you share a workbook with others in your office, you will probably want to make sure that some of the worksheets don't ...

Discover More

Using Overtype Mode

When you type information into a document, what you type normally is inserted just the left of the insertion point. Word ...

Discover More

Enabling and Disabling Windows Features

Want to make sure that Windows is trim and fit, using only those features you routinely use? Here's how to enable or ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Excel Self-Tests

Need to find out how good you are with Excel? Here are some places you can check out to quiz yourself.

Discover More

Frequent Workbook Recovery Prompts

When you open a workbook, Excel examines that workbook to make sure it can understand the data it contains. This can lead ...

Discover More

Starting in Safe Mode

By using a command-line switch, Excel can be started in safe mode. This means that the program is loaded with bare-bones ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 8 - 3?

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

Ron

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
Subscribe

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.