Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    strange behaviour... (Excel 97/2000)

    Hello everybody!

    Today I've experienced quite an interesting phenomenon. Let me describe it first:
    We create a new spreadsheet. Now we write a single value or text in, let's say, cell(B3). Now we mark another cell and press Ctrl + End. cell(B3) will be the result of selection. Now we enter another value into another cell, let's say cell(F7). Pressing Ctrl + End will lead us now to cell(F7). If we now delete this cell by pressing Del while it is selected, this very cell is empty again. But pressing Ctrl + End still leads us to this cell though it is empty. This seems to mean, that Excel still stores any kind of information about this cell, it seems to be not empty.
    Does anybody know about this effect and has an idea how to prevent it?
    Any comment is appreciated!
    Thanks for trying.
    Greetings, Porley

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

    Re: strange behaviour... (Excel 97/2000)

    This effect is fairly well known, but preventing it is another matter. To rectify it you can Clear all columns and rows outside the real last used cell. You then need to save and close the workbook. Next time you re-open the file it should be ok.

    I find the following code works without saving and closing :<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>

    That actually works on all sheets, and could be simplified to work on just the active sheet.

    Andrew C

Posting Permissions

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