Excel file is too large even after it's empty

[Imran]

I deleted all sheets from my excel file.
There are no VB codes associated with the excel file.
I only have one sheet, with no data.
However, the size of my file is still 2M.

When I open an empty excel file, it's size is usually 80K
Bytes.

Why is my empty file too large?

 

[Mike]

Why would you be concerned about the size of a Supposedly
empty workbook. Delete the workbook and start a new one.

 

[RWN]

Probably because xl gets "confused" as to the used range once you've
deleted cells.
On the sheet do a "Ctrl-End" and see where it takes you. You'll probably
find that you're "out in the weeds someplace".
What I do to get rid of the unused (as I see it) space is to do the
Ctrl-End then select the rows from the actual end of my data to the row
where xl thinks the end is and delete them. Do this for the columns as
well, if applicable.
Then, very important, save the workbook.

Now do a Ctrl-End and you should be at the actual end of your data.

HTH

 

[David McRitchie]

A macro solution that will reset the used cell range and not force
deletions should you actually have formulas in hidden areas.

Sub Reset_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub

 

[RWN]

Dave;
I've added this macro to my collection (even put a button on my
toolbar - I like it!), but I have a question.
This works great if I have deleted rows(columns) but not if I've simply
"Cleared Contents".
I was wondering how Excel distinguishes between an unused cell and one
that was used, but has been cleared?
I thought it may have been something to do with the formatting but I
tried "Clear->All" and it still thinks that the cell is "used".
Not a big deal (perhaps a stupid question) but just wondering.

--
Regards;
Rob
------------------------------------------------------------------------

A macro solution that will reset the used cell range and not force
deletions should you actually have formulas in hidden areas.

Sub Reset_all_lastcells()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim sh As Worksheet, x As Long
For Each sh In ActiveWorkbook.Worksheets
x = sh.UsedRange.Rows.Count 'see J-Walkenbach tip 73
Next sh
End Sub

<snip>

 

[David McRitchie]

Did you look at my page, you would find a reference to
http://www.j-walk.com/ss/excel/tips/tip73.htm
which doesn't explain it either. But if Excel doesn't do what it is
supposed to do it is kind of hard to figure out why something else
related does or doesn't work.

Didn't realize that John had a macro to do all the sheets as well,
but I prefer to have control over when something is going to happen.

 

[RWN]

Yes, I did look at the page you referenced, and you are right, it
doesn't address the issue.

I wasn't saying XL doesn't do what it's supposed to do, in fact (more
often than not) I've found that many of the problems I've had with xl
were my own fault!
Again, I was just curious as to how it determines that a cleared cell is
not the same as a deleted cell.

For all I know there's probably a good reason for its behaviour, and I
just can't see it.

And you're absolutely right about wanting to have control as to when
something happens.

Thanks.

 

[Dan-note]

A belated comment in case it helps others on a possible solution.

I had an excel file with pivot tables that referenced external source data. I tried to save an extract version of the workbook that did not contain the pivot tables or reference the external data, but the workbook file was larger than expected and did not reduce much in size even if all the sheets were deleted. The problem appeared to be that cross-references to the external data were still retained somewhere internally in the workbook. One solution was to go back to a version of the spreadsheet before deleting the sheets containing the pivot tables and other external references, then transfer the external source data into the workbook, making sure there were no additional external links (menu item: Edit / Links), and THEN deleting the various sheets I didn't need including the data that had been transported back in and was not needed for the extract version of the workbook. CAUTION - use a separate copy of the workbook and the source data copied into a separate directory so that the original workbook and its reference links to the source data are not messed up.

Other potential solution to a too-large spreadsheet include looking at the menu item: "format / sheet / unhide". and looking for hidden objects under the menu option: "tools / macro / visual basic editor" but that is for more experienced or risk-taking users.

Good luck.