+ Reply to Thread
Results 1 to 10 of 10

I cant for the life of me delete excess rows.

  1. #1
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2010
    Posts
    176

    I cant for the life of me delete excess rows.

    I have two worksheets with over a million empty rows of data making the workbook 8mb in size. I tried deleting the rows to ensure theres no data but it doesnt get rid of them. I know this is the cause of my file size because when i deleted one of the worksheets it cut 3mb of my workbook size. Unfortunately my second worksheet has a lot of data lookups tied to it and can not be as easily deleted. How can i get rid of all the empty rows of data?

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013.. mostly
    Posts
    15,888

    Re: I cant for the life of me delete excess rows.

    Try this on a BACKUP copy of your file...

    https://www.techrepublic.com/blog/mi...orkbook-bloat/
    Glenn



  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    2013, Office 365 ProPlus
    Posts
    1,903

    Re: I cant for the life of me delete excess rows.

    It's likely format issue.

    Select seemingly empty range and use Editing ribbon tool and Clear -> All.

    Then delete range and save file. Check if file size changed.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,984

    Re: I cant for the life of me delete excess rows.

    Post withdrawn- Lee
    Last edited by leelnich; 01-03-2018 at 01:43 PM.
    Please click the Add Reputation star below any helpful posts, and mark your thread as SOLVED (Thread Tools, up top) once you have your answer. Thanks!-Lee

  5. #5
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: I cant for the life of me delete excess rows.

    Quote Originally Posted by leelnich View Post
    Post withdrawn- Lee
    I checked but couldnt find any. Is there a way to check references to a specific cell?

  6. #6
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,984

    Re: I cant for the life of me delete excess rows.

    Actually, Excel doesn't "charge" you for referencing cells beyond the last used cell. And if a directly-referenced cell was deleted (when you removed "extra" rows), the referencing formula would return a #REF! error.
    Last edited by leelnich; 01-03-2018 at 01:55 PM.

  7. #7
    Valued Forum Contributor leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    1,984

    Re: I cant for the life of me delete excess rows.

    Quote Originally Posted by mikey42979 View Post
    ...Is there a way to check references to a specific cell?
    Yes. Select the cell, then click Trace Dependents on the Formula ribbon.

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    1,629

    Re: I cant for the life of me delete excess rows.

    You say you're sure it's the empty rows causing the issue but also that there are a lot of data lookups, so maybe it's not the rows?
    Try copying the non-empty cells (A1 to bottom-right) and pasting those into a new file. Don't worry about any errors at this stage (maybe caused by Names, etc which won't exist in the new file). Save the new file and check its file size.
    If the new file is also very large, then it's something in the formulae which is causing it to be so big.

    Apologies if you've already tried this - it's just something which I use to troubleshoot bloated files.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013.. mostly
    Posts
    15,888

    Re: I cant for the life of me delete excess rows.

    Indeed. I pointed this out at post 2....

  10. #10
    Forum Contributor
    Join Date
    01-05-2013
    Location
    Kuala Lumpur, Malaysia
    MS-Off Ver
    Excel 2016
    Posts
    149

    Re: I cant for the life of me delete excess rows.

    You can do the suggestion by Aardigspook by copying to a new sheet, i.e. in the same file, then delete the problematic sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1