Where is the End of the Excel 2010 worksheet?

Topic

New Reply

Sometimes when I press CTRL+End on a worksheet, the cursor goes to a place far beyond the last non-blank row and column of the worksheet. I used to know how to fix this in older versions of Excel, but I’ve forgotten.

Who can help?

Reply | Quote

Replies

Usually just deleting the rows and columns below and to the right of the actual last cell, and then saving the file will fix this.

Reply | Quote

True. But suppose there is some non-visible character 500 rows below the actual last cell and lots of columns to the right.

If one “knows” the last cell (I know there is nothing beyond V258 let’s say), is there some way to quickly select column W to the end and row 259 to the end (I say “to the end” because different versions of Excel have extended the number of rows and columns in a sheet) and delete those? Is there some way to get Excel to show the “offending” cell so you can check if it really is something one forgot?

I’ve had the same problem as Lou mentions and found it non-trivial to verify that what Excel is reporting is due to some glitch versus me having forgotten that I hid some table beyond the “normally visible” cells.

Fred

Reply | Quote

Lou,

Here is an Excel add-in that resets the last cell in excel

http://xsformatcleaner.codeplex.com/

This second link shows how to use it

http://support.microsoft.com/kb/244435

Reply | Quote

Select W259 then Ctrl+Shift+End.

Reply | Quote

Thanks Rory. Didn’t know about Ctrl+Shift+End in Excel.

But still not sure about what to do once I’ve selected that cell. I tried a quick experiment. If my hypothetical V258 is truly my last cell, then selecting W259 and Ctrl+Shift+End selects a rectangle going “backwards” – V258:W259. But if there is something like a cell with a space in it, say at AA270, then Ctrl+Shift+End creates a rectangle from W259:AA270. Is this supposed to be what’s happening? I can see that doing this continually will allow me to “see” those “invisible” cells and I can eventually get rid of all cells to the right and below V258.

Fred

Reply | Quote

If Ctrl+End isn’t taking you past the end of your data (the selecting ‘backwards’ scenario) then you don’t need to do anything. If it is, then once you’ve done Ctrl+Shift+End you can delete those excess rows/columns and save the file. Most of the time, that will cure the problem.

Reply | Quote

This macro be tucked in a standard module or your personal.xlsb and launched from a keyboard shortcut or an icon on the Quick Access toolbar. It will delete all the rows and columns beyond the used range, save, and close the file. As rory states, when the file is re-opened, the end cells have been reset

Code:

Sub ResetEndCell()
    With ActiveCell
    Range(Rows(.SpecialCells(xlLastCell).Row + 1), Rows(Rows.Count)).Delete
    Range(Columns(.SpecialCells(xlLastCell).Column + 1), Columns(Columns.Count)).Delete
    End With
    ThisWorkbook.Close SaveChanges:=True
End Sub

Reply | Quote

Thanks Rory.

Enjoy London and the blue skies.

Fred

Reply | Quote