Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    CTRL-End sends me too far away. (Office 97 SR2)

    I must be doing (or have done), something wrong, but cannot realize what.
    The scenario :
    Occasionally, I go back to update a spreadsheet I created before.
    Although I see that the last cell showing an edit is , lets say, M602, when keying CTRL-End, it takes me to N2067.
    I have to return by Page Up, what wastes the time I was trying to save.
    I realize I could be dragging something from the original editing, but even highlighting and deleting all rows between 603 and 2068, doesn

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

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    In the thread starting at <post#=133048>post 133048</post#>, you will find some ideas: VB code to delete unused rows and columns. Note: Jan Karel Pieterse's tip to save as HTML and re-import is meant for Excel 2000 / XP. In Excel 97, you may lose features of your workbook.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    When you highlight and delete the rows, Save, Close and re-open the workbook and the superfluous rows shoul dno longer be included in the usedrange.

    It seems that if a reference the UsedRange is made via VBA, Excel wakes up and resets the last used cell. Something like the following should help : <pre>Sub ResetUsedRange()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim sht As Integer
    sht = ActiveSheet.Index
    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ActiveSheet.UsedRange
    Next
    ActiveWorkbook.Worksheets(sht).Activate
    Application.ScreenUpdating = True
    End Sub</pre>

    Andrew C

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Andrew I do not think its necessary to save, close and reopen, just saving works in 2000 and (from memory) in 97.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Even used to work in XL5.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    So what am I doing wrong?

    I too find that once an XL 97 SR2 spreadsheet has been 'dirtied' at cell Nn then no matter how many times I Edit Delete or Edit Clear All, save and reopen, save and don't reopen etc. etc., ctrl end will always take me to the empty distant corner of my spreadsheet defined by cell Nn.

    stuck

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Normally, edit, delete and a save should reset it.
    I've heard that on occasion it does not work, but have never had it myself.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Lucky you! This irritation is the default round these parts.

    stuck

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Thanks to all the people who replied to my question.
    I will try the solutions suggested and let you know the results.

    Cheers.

    Daniel Rozenberg.

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

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Can you attach a workbook that displays this so we can look at it?
    Legare Coleman

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    You have a cell named Nn that is "outside" normal range of data?

    Having format in a "distant cell", or naming the cells will keep excel believing it is part of the used range

    Steve

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    I've had this problem many times. At least once the solution was to clear the formatting in the row and column AFTER the apparent last cell (where CTRL-END takes you). Doesn't always work -- but I haven't found any solution that always works.

  13. #13
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    I've found that selecting all ROWS from the last 'real' empty cell to a couple past the Excel 'imaginary-end' cell and then Deleting those rows,
    then repeating for COLUMNS works for most cases. (Presumably as it ensures that formats, contents, conditional formating and name ranges are all cleared.)

  14. #14
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Try this one.

    stuck

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

    Re: CTRL-End sends me too far away. (Office 97 SR2)

    Selecting columns A:E and selecting Edit/Delete/All, then selecting rows 1:228 and selecting Edit/Delete/All seems to reset the last cell.

Page 1 of 2 12 LastLast

Posting Permissions

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