xls File size way too big

[Chuck Jones]

I am having a problem with xls file sizes. I have
several virus-free files which are sized way out of
proportion to their content. One file has one worksheet
with 30 lines of text. No numbers, formulae, add-ins,
macros etc. It is 601KB. A bigger (6 worksheets) version
with similar properties is over 5MB. I have files
(created earlier but not updated recently), with much more
content (say, 30 times more content) which are only 75KB.
What's up? How can I tame this? What should I look for?

 

[Norman Harker]

Hi Chuck!

Here's two potential causes with solutions:

Sometimes the size of a Excel file can blow out to several megabyte
with very little apparent change to the cell. The most common problem
relates to the last cell of each worksheet.

False End Row or Column of Data


Use:



Edit > GoTo > Special

Check "Last Cell"

OK



Does it take you a lot further than you should go? If, "Yes", then you
need to delete all rows below the "real" bottom of your data and all
columns to the right of the furthermost column used by your data.



For each sheet in turn:



To reset the last row:

Select cell A one row down from the last cell with data.

Press and continue to hold down Ctrl + Shift keys, and then press

the Down Arrow key.

Right-click > Delete

Check "Entire Row"

OK.

To reset the last column:

Select Row 1 of the last column with data in it.

Press and continue to hold down Ctrl + Shift keys, and then press

the Right Arrow key.

Right-click > Delete

Check "Entire Column"

OK.

Having checked / done each sheet, Save the file although with Excel
2000 and before you have to use:



Save, Close, Open, Save



A VBA subroutine that does this is:



Sub DeleteUnused()

'Debra Dalgleish and others have posted this:

Dim myLastRow As Long

Dim myLastCol As Long

Dim wks As Worksheet

Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets

With wks

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



Bloated VBA Code


Apparently, VBA code can get bloated, although I've not seen this.


Rob Bovey's codecleaner cleans up some of the junk left over in the
VBA modules. You can find it here:

http://www.appspro.com/

Try these and report back. You didn't report your Excel Version.
Certainly one improvement between (I think) Excel 2000 and Excel 2002
was a more efficient way of reducing file size.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Don Guillett]

try this
Sub DeleteUnusedRon() 'Ron de Bruin
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
With wks
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

 

[George Nicholson]

Another possibility is to make sure your worksheet is saved in the "simple"
Microsoft Excel Workbook format. I believe that saving in any of the "dual"
formats (Excel 97/2002 and 5.0/95) can create significantly larger files
(although probably not as large as you are describing).

 

[TBA]

Formatting applied to an entire worksheet can hog major memory space. Try
hitting Ctrl-End on each worksheet an see what cell it takes you too. You
may be surprised.

-gk-

 

[Chuck Jones]

Thanks all. You hit the target dead on.

Chuck

-----Original Message-----
Hi Chuck!

Here's two potential causes with solutions:

Sometimes the size of a Excel file can blow out to several megabyte
with very little apparent change to the cell. The most common problem
relates to the last cell of each worksheet.

False End Row or Column of Data


Use:



Edit > GoTo > Special

Check "Last Cell"

OK



Does it take you a lot further than you should go? If, "Yes", then you
need to delete all rows below the "real" bottom of your data and all
columns to the right of the furthermost column used by your data.



For each sheet in turn:



To reset the last row:

then press
the Down Arrow key.






To reset the last column:



then press
the Right Arrow key.






Having checked / done each sheet, Save the file although with Excel
2000 and before you have to use:



Save, Close, Open, Save



A VBA subroutine that does this is:



Sub DeleteUnused()

'Debra Dalgleish and others have posted this:

Dim myLastRow As Long

Dim myLastCol As Long

Dim wks As Worksheet

Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets

With wks

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



Bloated VBA Code


Apparently, VBA code can get bloated, although I've not seen this.


Rob Bovey's codecleaner cleans up some of the junk left over in the
VBA modules. You can find it here:

http://www.appspro.com/

Try these and report back. You didn't report your Excel Version.
Certainly one improvement between (I think) Excel 2000 and Excel 2002
was a more efficient way of reducing file size.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
for?


.

 

[Norman Harker]

Hi Chuck!

Good to hear that your file sizes are down to "normal".

One gripe I suppose is that if I can reduce the size of a file by
significant amounts using (e.g.) WinZip, why can't Microsoft use a
similar approach to its file saving? I suppose it's a time penalty
thing. But it is useful to remember when mailing Excel files that a
winzipped version will be a lot smaller and less likely to offend file
size limitations. But then some settings reject zipped files so it
won't always work.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.