Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2006

Tip: Looking for answers? Try searching our database.

Why are some spread sheets so much larger?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jim Moberg - 12 Oct 2006 21:29 GMT
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?
Gord Dibben - 12 Oct 2006 22:02 GMT
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,
>
[quoted text clipped - 7 lines]
>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?

Gord Dibben  MS Excel MVP
Jim Moberg - 12 Oct 2006 22:26 GMT
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????

> Could be the used range Excel estimates on a sheet.
>
[quoted text clipped - 27 lines]
>
> Gord Dibben  MS Excel MVP
Gord Dibben - 13 Oct 2006 00:50 GMT
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

>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????
[quoted text clipped - 30 lines]
>>
>> Gord Dibben  MS Excel MVP
Jim Moberg - 13 Oct 2006 14:19 GMT
I double checked and I didn't get those mixed up.

> You sure you don't have your MB's and KB's mixed up.
>
[quoted text clipped - 38 lines]
> >>
> >> Gord Dibben  MS Excel MVP
Gord Dibben - 13 Oct 2006 21:56 GMT
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

>I double checked and I didn't get those mixed up.
>
[quoted text clipped - 40 lines]
>> >>
>> >> Gord Dibben  MS Excel MVP
Opinicus - 13 Oct 2006 09:53 GMT
> Could be the used range Excel estimates on a sheet.
> Sometimes gets it wrong.
[quoted text clipped - 4 lines]
> NOW the IMPORTANT step........save the workbook.
> Sometimes save, close and re-open to see changes.

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?

Signature

Bob
http://www.kanyak.com

Otto Moehrbach - 13 Oct 2006 13:54 GMT
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

>> Could be the used range Excel estimates on a sheet.
>> Sometimes gets it wrong.
[quoted text clipped - 11 lines]
> How does this inflation happen and how can it be prevented? Could the
> shrinking operation be automated with a macro I wonder?
Jim Moberg - 13 Oct 2006 14:20 GMT
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?

> Bob
>     This "inflation" thing is man made.  What Excel "thinks" is the extent
[quoted text clipped - 114 lines]
> > How does this inflation happen and how can it be prevented? Could the
> > shrinking operation be automated with a macro I wonder?
aaron.kempf@gmail.com - 17 Oct 2006 23:54 GMT
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!

> > Could be the used range Excel estimates on a sheet.
> > Sometimes gets it wrong.
[quoted text clipped - 11 lines]
> How does this inflation happen and how can it be prevented? Could the
> shrinking operation be automated with a macro I wonder?
Don Guillett - 13 Oct 2006 13:31 GMT
look here
http://www.contextures.com/xlfaqApp.html#Unused
Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hello,
>
[quoted text clipped - 11 lines]
> embedded
> in these larger spread sheets that's causing them to be so much larger?
Jim Moberg - 16 Oct 2006 15:22 GMT
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?

> look here
> http://www.contextures.com/xlfaqApp.html#Unused
[quoted text clipped - 13 lines]
> > embedded
> > in these larger spread sheets that's causing them to be so much larger?
Don Guillett - 16 Oct 2006 15:28 GMT
see Gord's answer

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Don please forgive my ignorance here but I'm not seeing where I am to put
> the
[quoted text clipped - 24 lines]
>> > embedded
>> > in these larger spread sheets that's causing them to be so much larger?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.