Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot delete rows (2003)

    The spreadsheet is about 10000 rows. I created a subtotal function, then collapsed the spreadsheet to show only the level "2" items. Inserted a blank column and entered a formula. I inadvertently copied it to the bottom of the spreadsheet instead of to the bottom of the data area (1000 0 rows). No problem -- just delete the formula from from cells in rows 10000 to 65000... Oops -- that's not sufficient. When I try to un-collapse the subtotals, Excel issues an error message that it "cannot shift objects off sheet". When I press Ctrl-End, the cursor goes all the way to row 65000+

    I tried to select all the rows below 10000, but Excel became unresponsive, and I had to shut it down using Task Manager. It appears to be impractical to select and delete more than about 100~200 rows at a time before Excel becomes unresponsive.

    Is there an easy way to delete all the rows between 10000 and 65000?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: Cannot delete rows (2003)

    Does it work better if you set calculation to Manual in the Calculation tab of Tools | Options...? (Don't forget to set it to Automatic again afterwards).

    If that doesn't work, perhaps using VBA?
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Press Ctrl+G to activate the Immediate window.
    - Type
    <code>
    Range("A10001:A65536").EntireRow.DeleteContents
    </code>
    or
    <code>
    Range("A10001:A65536").EntireRow.Delete
    </code>
    and press Enter.

  3. #3
    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

    Re: Cannot delete rows (2003)

    If Hans' method doesn't work, you may need to remove the subtotals, delete the blank rows, then reapply the subtotals.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot delete rows (2003)

    Setting recalc to "manual" made no difference.

    I have launched the VBA solution (Range("A10001:A65536").EntireRow.Delete) and my CPU has been pegged at 100% utilization for the past 5 minutes according to Windows Task Manager. No indication how long it will remain pegged at 100%. While it's deleting those rows, Excel is completely unresponsive to other inputs.

    It sure is a lot easier to inadvertently *create* those rows than it is to delete them!

    Thanks once again for your help.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot delete rows (2003)

    FYI

    I got impatient, and killed the Excel process after about 10 minutes. Re-jigged the VBA statement to delete 10000 rows, which took about 3-4 minutes, so deleting the 50000+ rows would probably take about 15-20 minutes. Anyway, I can now work with the spreadsheet again.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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