File is too big...

D

Denise B

Hello,

I have a report that I am going to run every morning that
has a lot of formulas. The document 800kb so before I e-
mail it I copy and Paste special to get rid of all of the
formulas. Even though I have done this the document size
stays the same. I do this with a few other reports so I
find it a bit strange. I have even completely emptied the
report of all numbers and checked the size, still 800kb???
I have pasted everything then into note pad and a lot of
weird stuff showed up.

Any ideas?

Thx,

Denise
 
D

Don Guillett

try this and save to a different name to test.

Sub DeleteUnused() 'Debra Dalgleish
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
 
M

Mark

Hmmm,

Check for the last selected cell
Ctrl+Shift+End
You can then see where you are. I received a 4mb file
recently and did this action. It took me to the 60,000th
row but the worksheet had only 2 rows of data. The sender
must have deleted data but did not "clear all formats" to
get rid of the formatting of the cells.
This might be your problem too.

Mark
 
Top