Large file size in Excel

[Guest]

I've got a worksheet that is blank, and the Ctrl-End key combination jumps to cell A1. I originally pasted 5 colums by 138 rows of data into this spreadsheet, then erased the columns completely by using 'Ctrl+-'. I've also selected the entire sheet and used Edit->Clear All. All this to no avail. I have an empty spreadsheet that takes up 900 KB, but zips to 30 KB

Any suggestions on how to reduce the file size is welcome.

 

[Paul]

I've got a worksheet that is blank, and the Ctrl-End key combination jumps

to cell A1. I originally pasted 5 colums by 138 rows of data into this
spreadsheet, then erased the columns completely by using 'Ctrl+-'. I've
also selected the entire sheet and used Edit->Clear All. All this to no
avail. I have an empty spreadsheet that takes up 900 KB, but zips to 30 KB.

Any suggestions on how to reduce the file size is welcome.

If it really is all blank, then why not just delete the file and forget it?
If you want the original 5x138 block of data, copy this block and paste into
a new workbook, save and see how big that file is.

 

[Guest]

Paul,

That's exactly what I did. I copied the block of data from the original file, got the large file size, so I erased the block, erased all formatting, so that it's only blank, and the file size is still large.

The reason I am playing with the blank file is that if I can't get that file size reduced, I don't know how I will get the file size of the spreadsheet containing that block of data reduced.

Please suggest solutions. Thanks in advance for your help.

Jon

----- Paul wrote: -----

I've got a worksheet that is blank, and the Ctrl-End key combination jumps

to cell A1. I originally pasted 5 colums by 138 rows of data into this
spreadsheet, then erased the columns completely by using 'Ctrl+-'. I've
also selected the entire sheet and used Edit->Clear All. All this to no
avail. I have an empty spreadsheet that takes up 900 KB, but zips to 30 KB.
If it really is all blank, then why not just delete the file and forget it?
If you want the original 5x138 block of data, copy this block and paste into
a new workbook, save and see how big that file is.

 

[Dave Peterson]

Debra Dalgleish has some notes and pictures at:
http://www.contextures.com/xlfaqApp.html#Unused

It'll help you reset that last used cell.

(but if it's really blank, why do you want to save it at all? <vbg>)

 

[Guest]

As I said in my other post, I actually have data I want to save, but the file size is too large. I've already tried copying only the block containing the data of interest, with no luck. I then erased the block just to see what happens, and the file size remains too large. The question here is not with trying to save a blank spreadsheet for fun, but to fix the problem MS Excel has with generating a large file with based on whatever Excel wants to do instead of data I actually put into the spreadsheet

Anybody who can help with this question? Thanks in advance

I have already tried resetting the last cel, copying the block of data, etc. None of that works. Or is this just an unfixable error due to Excel? Has anybody ever seen this type of problem in Quattro or other spreadsheet programs, or is it just typical of Microsoft (I had similar problems with MS Visio, and had to use a 3rd party tool to fix it because Microsoft could not fix it)

Jo

----- Dave Peterson wrote: ----

Debra Dalgleish has some notes and pictures at
http://www.contextures.com/xlfaqApp.html#Unuse

It'll help you reset that last used cell.

(but if it's really blank, why do you want to save it at all? <vbg>


Jon wrote

 

[Dave Peterson]

You said you fixed the last cell. So when you hit ctrl-end, it took you to what
you thought the bottom right cell should be?

Is there any chance that you have any objects on you
worksheet--pictures/shapes/comments/etc?

Option Explicit
Sub testme01()

Dim myShape As Shape
Dim resp As Long

MsgBox "There are " & ActiveSheet.Shapes.Count _
&" shapes on this worksheet"

For Each myShape In ActiveSheet.Shapes
With myShape
'resp = vbYes
resp = MsgBox(prompt:="delete " & .Name _
& vbLf & "Near " & .TopLeftCell.Address(0, 0) & "?", _
Buttons:=vbYesNo)
If resp = vbYes Then
.Delete
End If
End With
Next myShape

End Sub

If there are millions of shapes and you don't want to keep any, delete that
"resp = msgbox" line and uncomment the "resp = vbYes" line.

 

[Guest]

Dave

Thank you very much for your help. The ctrl-end key took me to A1, so that did not change anything. Running the macro, I found 32 shapes (no idea what they were), and deleted all of them. Now my file size is 20 Kb instead of 1 Mb

Regards

Jo

----- Dave Peterson wrote: ----

You said you fixed the last cell. So when you hit ctrl-end, it took you to wha
you thought the bottom right cell should be

Is there any chance that you have any objects on yo
worksheet--pictures/shapes/comments/etc

Option Explici
Sub testme01(

Dim myShape As Shap
Dim resp As Lon

MsgBox "There are " & ActiveSheet.Shapes.Count _
&" shapes on this worksheet

For Each myShape In ActiveSheet.Shape
With myShap
'resp = vbYe
resp = MsgBox(prompt:="delete " & .Name
& vbLf & "Near " & .TopLeftCell.Address(0, 0) & "?",
Buttons:=vbYesNo
If resp = vbYes The
.Delet
End I
End Wit
Next myShap

End Su

If there are millions of shapes and you don't want to keep any, delete tha
"resp = msgbox" line and uncomment the "resp = vbYes" line



Jon wrote

 

[Dave Peterson]

If you still have the original file, you may want to make them visible to see if
they were important.

you could change these lines:
If resp = vbYes Then
.Delete
End If
to
If resp = vbYes Then
.visible = true
End If

(And maybe change the prompt if it's important to you.)