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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<snip>

 

[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.
--
Regards;
Rob
------------------------------------------------------------------------

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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.

--

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:

http://www.mvps.org/dmcritchie/excel/search.htm

<snip>