Excel is saving blank rows, causing extremely large files

Question

My coworker's Excel spreadsheets in one workbook have anywhere from 200 to 1000 rows on each spreadsheet.  However, the blank rows for the entire length of the available rows (65500, using 2010 but saving for other versions) are being saved as if they have data (maybe a space?) entered in them.  The saved file size was 69MB.   I went in and deleted the contents of the empty cells (ikr) and resaved, managing to a file size of 8MB.  What on earth caused Excel to think there is data in the cells?  Why didn't it automatically compact the empty cells?

Any help is appreciated!!

Rita Brasher

Project Engineer, FedEx Express

Answer 1

in the name box (to the left of the formula bar formula text box) put in something like

1000:65536  <enter>

hit delete

where 1000 marks the first row you believe is empty.

make 65536 to be the last row you want deleted

You can get this number by do F5 then special and choosing last cell and noting the row.

reference entering the row range in the namebox

this will select all the designated rows and then you delete them.

 Now save the file.  this should cause Excel to revise what it considers to be the last used row. 

Do this on every sheet

or you could write a macro to do this - fortunately Debra Dalgleish has already done this:

http://www.contextures.com/xlfaqApp.html#Unused

--

Regards,

Tom Ogilvy

Answer 2

Rita wrote:

That makes sense.  I know he's importing data from somewhere and then, at some point, does a copy>paste special>values operation, so perhaps it's retaining the null string result from earlier.  Is there an easier way to resolve than selecting every row and deleting?

I don't know if there are operation limits (i.e. a limit on the number of cells that Find All will show), but the following procedure worked with a small range of cells.

1. Find all "empty" cells, to wit:  press ctrl+F, do not enter anything in the Find What field, then click on Find All.  Note that that finds cells with constant null strings as well as truly empty cells (no constant and no formula).  It does not find cells with formulas that return the null string, as we intend.
2. Select everything in the Find All results, to wit:  press ctrl+A.
3. Clear Contents, to wit:  click on Clear on Home ribbon, then click on Clear Contents.

Note:  At that point, ctrl+End will still go to the end of the "empty" rows.  Excel does not adjust that location until we save the file or access ActiveSheet.UsedRange in VBA.

Answer 3

Rita wrote:

My coworker's Excel spreadsheets in one workbook have anywhere from 200 to 1000 rows on each spreadsheet.  However, the blank rows for the entire length of the available rows (65500, using 2010 but saving for other versions) are being saved as if they have data (maybe a space?) entered in them.  The saved file size was 69MB.   I went in and deleted the contents of the empty cells (ikr) and resaved, managing to a file size of 8MB.  What on earth caused Excel to think there is data in the cells?  Why didn't it automatically compact the empty cells?

Maybe the "blank rows" only appeared that way because the cells contained null strings.  There many ways that can happen.

Perhaps the cells contained formulas that returned the null string, and you didn't notice.

Alternatively, here is an experiment that will show how zero-length constant null strings might arise.  In cell (A1), enter the formula ="", then copy and paste-special-value back to A1.

A1 certainly appears empty.  But ISBLANK(A1) returns FALSE, and 2*A1 returns a #VALUE error, indicating that A1 does indeed contain a constant null string.

For a more dispositive explanation, I would need to see the original Excel file.

Answer 4

That makes sense.  I know he's importing data from somewhere and then, at some point, does a copy>paste special>values operation, so perhaps it's retaining the null string result from earlier.  Is there an easier way to resolve than selecting every row and deleting?