Changing where ctrl+end thinks the end of a document is

In Excel, I often use ctrl+end to get to the end of the spreadsheet. But a lot of times a large amount of data gets deleted, and ctrl+end still wants to take me to the end of where that data used to be, rather than where the data actually ends after the delete. Is there a way to get to the actual end of the data with a keyboard shortcut, rather than just scrolling down and looking for it?

I’ve never found a way around that. Annoying, isn’t it?

This can vary, I think, by Excel version and platform, but there’s a delete that just clears the contents of the cells, and there’s delete that actually deletes the rows. Maybe if you can find the row delete button, that might help you.

Sometimes you can select a cell and ctrl-down will take you to the last row in that column with data, although if you have an empty cell in a row in that column, it will stop there and you may have to keep ctrl-downing until you go too far. If you have a column with data in all rows, this can work OK.

Use “Clear” to make sure you got rid of everything in those “empty” cells. If you do that, Excel shouldn’t take you to the end of the worksheet in whichever direction you’ve chosen.

There are two issues. The first is that (as others have mentioned) clearing the cell contents is not enough; you have to make sure the cell is completely empty of all formatting and other usually-invisible information. The easiest way to do that is to select the apparently-empty rows or columns, right-click and delete them (which then moves new blank cells upward and leftward).

The second issue is that Excel doesn’t automatically update its idea of the active range of a worksheet, even when you do this. The easy way to force this update is just to save the file, which forces this update. If you don’t want to do this you can run the Visual Basic command ActiveSheet.UsedRange, which appears to be what actually calculates the range. (Attach it to a macro and bind it to a button if you like.)

That is exactly what I needed, thanks so much.

Concur.