+ 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
    181

    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 & 2016
    Posts
    19,046

    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
    3,061

    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
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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.
    Clicking the Add Reputation star below any helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion. (Thread Tools above Post # 1) Thanks!-Lee

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

    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
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    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,838

    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 & 2016
    Posts
    19,046

    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
    155

    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