Results 1 to 8 of 8
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Where is the End of the Excel 2010 worksheet?

    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?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Select W259 then Ctrl+Shift+End.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Rory.

    Enjoy London and the blue skies.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •