Why are some spread sheets so much larger?

J

Jim Moberg

Hello,

We receive one particular spread sheet from a client on a regular basis.
Typically they range in size from 15K - 60K so they don't have much
information in them. Some of them are over 3 meg in size and take a while to
open. I can't see any additional data in the worksheets and am puzzled as to
why this is happening.

I have tried selecting each worksheet in it's entirety and setting the font
color to black and don't see anything new. Could there be something embedded
in these larger spread sheets that's causing them to be so much larger?
 
G

Gord Dibben

Could be the used range Excel estimates on a sheet.

Sometimes gets it wrong.

Go to bottom row of real data range.

SHIFT + End + DownArrow to select to bottom of sheet.

Edit>Delete>Entire Row

Do same for columns to the right of your data.

NOW the IMPORTANT step........save the workbook.

Sometimes save, close and re-open to see changes.


Gord Dibben MS Excel MVP

Hello,

We receive one particular spread sheet from a client on a regular basis.
Typically they range in size from 15K - 60K so they don't have much
information in them. Some of them are over 3 meg in size and take a while to
open. I can't see any additional data in the worksheets and am puzzled as to
why this is happening.

I have tried selecting each worksheet in it's entirety and setting the font
color to black and don't see anything new. Could there be something embedded
in these larger spread sheets that's causing them to be so much larger?
Click to expand...

Gord Dibben MS Excel MVP
 
J

Jim Moberg

I did that and the file went from 3 mb to 59 mb. After closing and reopening
the file went from 59 mb to 118 mb. What the????
 
G

Gord Dibben

You sure you don't have your MB's and KB's mixed up.

How long did it take to open the 59MB file?

Never heard of a workbook gaining size when this operation is carried out.


Gord
 
O

Opinicus

Gord Dibben said:
Could be the used range Excel estimates on a sheet.
Sometimes gets it wrong.
Go to bottom row of real data range.
SHIFT + End + DownArrow to select to bottom of sheet.
Edit>Delete>Entire Row
Do same for columns to the right of your data.
NOW the IMPORTANT step........save the workbook.
Sometimes save, close and re-open to see changes.
Click to expand...

THANK YOU for that. A spreadsheet that I've been using for over five years
to keep track of my weight just shrank from 3,698,176 bytes to 121,856.
(Wish I could do the same...)

How does this inflation happen and how can it be prevented? Could the
shrinking operation be automated with a macro I wonder?
 
O

Otto Moehrbach

Bob
This "inflation" thing is man made. What Excel "thinks" is the extent
of the used range is a big driver in the size of your file. For example, do
this:
Open a new blank file.
Enter something in A1.
Then enter something in C5.
Then enter something in G10.
Now do Ctrl - End.
Excel jumps to G10. Excel thinks G10 is the last cell in the used range.
Excel is right.
Now delete the entry in G10.
You know that the last cell of "your" used range is now C5.
Do Ctrl - End.
Excel jumps to G10 and will always jump to G10 regardless of how many
entries before G10 you delete.
When you do this with hundreds of columns and thousands or rows and dozens
of sheets, the significance of Excel "thinking" what the used range is can
be staggering.
Yes, there is a VBA way of doing what Gord said to do. The macro is below.
HTH Otto
This code is from Debra Dalgliesh at:

http://www.contextures.on.ca/xlfaqApp.html#Unused

Sub ResetUsedRange()

Dim myLastRow As Long

Dim myLastCol As Long

Dim wks As Worksheet

Dim dummyRng As Range

Dim s As Shape

For Each wks In ActiveWorkbook.Worksheets

With wks

On Error Resume Next

For Each s In ActiveSheet.Shapes

s.Placement = xlMoveAndSize

Next s

On Error GoTo 0

myLastRow = 0

myLastCol = 0

Set dummyRng = .UsedRange

On Error Resume Next

myLastRow = _

.Cells.Find("*", After:=.Cells(1), _

LookIn:=xlFormulas, LookAt:=xlWhole, _

SearchDirection:=xlPrevious, _

searchorder:=xlByRows).Row

myLastCol = _

.Cells.Find("*", After:=.Cells(1), _

LookIn:=xlFormulas, LookAt:=xlWhole, _

SearchDirection:=xlPrevious, _

searchorder:=xlByColumns).Column

On Error GoTo 0

If myLastRow * myLastCol = 0 Then

.Columns.Delete

Else

.Range(.Cells(myLastRow + 1, 1), _

.Cells(.Rows.Count, 1)).EntireRow.Delete

.Range(.Cells(1, myLastCol + 1), _

.Cells(1, .Columns.Count)).EntireColumn.Delete

End If

End With

Next wks

End Sub
 
J

Jim Moberg

I double checked and I didn't get those mixed up.
Gord Dibben said:
You sure you don't have your MB's and KB's mixed up.

How long did it take to open the 59MB file?

Never heard of a workbook gaining size when this operation is carried out.


Gord
Click to expand...
 
J

Jim Moberg

Thanks for the info. I have never put vba code into an excel sheet before.
Do you just insert it into the sheet itself or is there some other place you
enter it?

Otto Moehrbach said:
Bob
This "inflation" thing is man made. What Excel "thinks" is the extent
of the used range is a big driver in the size of your file. For example, do
this:
Open a new blank file.
Enter something in A1.
Then enter something in C5.
Then enter something in G10.
Now do Ctrl - End.
Excel jumps to G10. Excel thinks G10 is the last cell in the used range.
Excel is right.
Now delete the entry in G10.
You know that the last cell of "your" used range is now C5.
Do Ctrl - End.
Excel jumps to G10 and will always jump to G10 regardless of how many
entries before G10 you delete.
When you do this with hundreds of columns and thousands or rows and dozens
of sheets, the significance of Excel "thinking" what the used range is can
be staggering.
Yes, there is a VBA way of doing what Gord said to do. The macro is below.
HTH Otto
This code is from Debra Dalgliesh at:

http://www.contextures.on.ca/xlfaqApp.html#Unused

Sub ResetUsedRange()

Dim myLastRow As Long

Dim myLastCol As Long

Dim wks As Worksheet

Dim dummyRng As Range

Dim s As Shape

For Each wks In ActiveWorkbook.Worksheets

With wks

On Error Resume Next

For Each s In ActiveSheet.Shapes

s.Placement = xlMoveAndSize

Next s

On Error GoTo 0

myLastRow = 0

myLastCol = 0

Set dummyRng = .UsedRange

On Error Resume Next

myLastRow = _

.Cells.Find("*", After:=.Cells(1), _

LookIn:=xlFormulas, LookAt:=xlWhole, _

SearchDirection:=xlPrevious, _

searchorder:=xlByRows).Row

myLastCol = _

.Cells.Find("*", After:=.Cells(1), _

LookIn:=xlFormulas, LookAt:=xlWhole, _

SearchDirection:=xlPrevious, _

searchorder:=xlByColumns).Column

On Error GoTo 0

If myLastRow * myLastCol = 0 Then

.Columns.Delete

Else

.Range(.Cells(myLastRow + 1, 1), _

.Cells(.Rows.Count, 1)).EntireRow.Delete

.Range(.Cells(1, myLastCol + 1), _

.Cells(1, .Columns.Count)).EntireColumn.Delete

End If

End With

Next wks

End Sub
Click to expand...
 
G

Gord Dibben

Very strange doings!!

To answer your post about Debra's macro.

With workbook open, hit ALT + F11 to open Visual Basic Editor.

Hit CTRL + r to open Project Explorer.

Insert>Module.

Paste the code into that module.

ALT + q to return to Excel

Tools>Macro>Macros.

Select the macro and "Run"


Gord
 
J

Jim Moberg

Don please forgive my ignorance here but I'm not seeing where I am to put the
code. I had already read through the instructions and all it says is to use
the code they provide. I don't see where it says to place the code. Is it
in the worksheet itself or somewhere else?
 
A

aaron.kempf

Excel is a disease and it shouldn't be used for data entry

use the right tool for the job and lose the training wheels!
 
Top