Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    End-Home (Excel xp)

    I have a question on "end-home". I understood that when you hit the End then Home keys, Excel brought you to the lowest bottom and right of your spreadsheet where there is something actually in the cells. Sometimes this works fine, but I don't understand how on some some sheets I end up "in the middle of nowhere" so to speak. The last row of information may be in row 130, but end-home brings me to 526 or something. Deleting the rows in between doesn't have any effect. How does this happen and what can I do to fix it and bring it to where it's supposed to be?
    Thanks!
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End-Home (Excel xp)

    Louise,

    Exporting a workbook to HTML and then importing it into Excel usually does a good job of "cleaning up".

    If that doesn't work, take a look at the threads starting at <post#=133048>post 133048</post#> and <post#=190787>post 190787</post#>.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End-Home (Excel xp)

    Hi Hans,
    Thanks for the reply. I tried saving as HTML and then reopening. Maybe I'm not doing it right but it didn't seem to work for me. Maybe it's because I have several worksheets in my workbook? I tried following the other posts, but I am not familiar with macros or VB at all. I assume that's what they are doing?

    As I re-read your post, did you literally mean to export and import? Maybe that's what I did wrong. I just saved as. Will keep working on it.

    Thanks again
    Louise

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End-Home (Excel xp)

    Can you post a copy of the workbook?
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End-Home (Excel xp)

    Hi Legare,
    Posted the spreadsheet as you requested. Due to confidentiality reasons, I deleted the information.

    Thanks to you also for trying to help!
    Attached Files Attached Files
    Louise

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End-Home (Excel xp)

    For some reason, Excel thinks that rows far down these sheets are in the UsedRange. It could be some kind of formatting in the cells of that row. Anyhow, here is a procedure that should return the sheets to normal.

    1- Press End Home to find where Excel thinks the last used row is located.

    2- Click on the row number to select the entire row.

    3- Scroll up to find the real last used row and hold down the shift key and click on the row number of the row beneath the real last row to select all of the rows from there down to where Excel thinks the last row is.

    4- On the Edit menu select Delete to delete all of those rows.

    5- Repeat the above for all of the sheets that have this problem.

    6- Save the Workbook.

    Things should now be back to normal. BTW, this behavior may not change until you do the save, so don't expect the problem to be fixed after step 4.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End-Home (Excel xp)

    The Cleanup procedure from <post#=133055>post 133055</post#> takes care of your problem; it more or less does what Legare describes automatically.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: End-Home (Excel xp)

    Thank you both.
    Have tried deleting the rows before and assumed it didn't work because I didn't save, close and reopen. Thanks.

    Hans,
    Is that information from post 133055 a macro? Haven't learned how to do that yet. Someday, when I can find the time, I'm going to learn macros! :-)

    Thanks!
    Louise

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End-Home (Excel xp)

    Louise,

    Yes, the code in <post#=133055>post 133055</post#> is a macro. If you can't or don't want to use macros, the manual process described by Legare will do what you want.

    If you have to do this kind of thing often, it will soon become tedious - that's where macros come in: they can automate routine actions. A good post to learn about macros is Legare's <!post=Personal.xls Tutorial, 118382>Personal.xls Tutorial<!/post>.

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: End-Home (Excel xp)

    Just a couple of comments:
    You are going down to the last "used" cell in the spreadsheet. Even if you delete the contents of the cell, Excel will remember the cell was once used. This is why I recommend that one uses another sheet as a scratchpad - not the the far off regions of the current sheet.
    Anyhow, if you delete the empty rows (not just their contents, but the cells) save the file, close the file and then reopen it - you should reset Excel's memory for the last cell in the file.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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