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?
Try this on a BACKUP copy of your file...
https://www.techrepublic.com/blog/mi...orkbook-bloat/
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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
Post withdrawn- Lee
Last edited by leelnich; 01-03-2018 at 01:43 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
I checked but couldnt find any. Is there a way to check references to a specific cell?Originally Posted by leelnich
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.
Yes. Select the cell, then click Trace Dependents on the Formula ribbon.
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
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - 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.
Indeed. I pointed this out at post 2....
You can do the suggestion by Aardigspook by copying to a new sheet, i.e. in the same file, then delete the problematic sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks