Why does File Size increase with reducing spreadsheet information?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get a spreadsheet about 4.6 MB, and try to delete any useless formula for a
table A2:K1000, but I get no idea why the file size for this spreadsheet
increases from 4.6MB to 5.6 MB, which does not make any logical sense.
Will I create some garbage information when I delete those table's formula?
Does anyone have any suggestions?
Thanks for any suggestions
Eric
 
I get 100 spreadsheets to check for resetting the usedrange.
Do you have any suggestions on how to create one spreadsheet to reset the
usedrange for a number of spreadsheets without manually inserting
DeleteUnused code on each sheet?
Thank you for any suggestions
Eric
 
Create a new workbook with that macro in it.

Save that workbook.

When you open a workbook where you have to try to reset that last used cell, you
can open the workbook with the macro.

Then activate any workbook you want.
Tools|macro|macros
select the macro
and click run

All the worksheets in that activeworkbook will be processed.
 
Thank you very much for your suggestions

I have created a new workbook with the macro - DeleteUnused. Since I need to
run for over 100 spreadsheets, do you have any suggestions macro coding on
how to open any spreadsheet one by one and apply macro - DeleteUnused on each?

What I intend to do is
First, open the workbook with the macro - DeleteUnused and run the macro
DeleteUnusedAll, which will perform following tasks

open any specific spreadsheet, then apply macro - DeleteUnused, Save & Close
it.
open the next specific sheet 1, then apply ...
....
open the next specific sheet 100 ...
Do you have any suggestions?

Thanks you for any suggestions
Eric
 
Are all the files in one folder?

If yes:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook

'change the folder here
myPath = "C:\my documents\excel\test\Schedules"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
'the opened file should be the activeworkbook
call DeleteUnused
TempWkbk.Save
TempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub

This goes in the same workbook as your DeleteUnused macro.
Thank you very much for your suggestions

I have created a new workbook with the macro - DeleteUnused. Since I need to
run for over 100 spreadsheets, do you have any suggestions macro coding on
how to open any spreadsheet one by one and apply macro - DeleteUnused on each?

What I intend to do is
First, open the workbook with the macro - DeleteUnused and run the macro
DeleteUnusedAll, which will perform following tasks

open any specific spreadsheet, then apply macro - DeleteUnused, Save & Close
it.
open the next specific sheet 1, then apply ...
...
open the next specific sheet 100 ...
Do you have any suggestions?

Thanks you for any suggestions
Eric
Click to expand...
 
Thank you very much for your suggestions
Eric

Dave Peterson said:
Are all the files in one folder?

If yes:

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim TempWkbk As Workbook

'change the folder here
myPath = "C:\my documents\excel\test\Schedules"
If myPath = "" Then Exit Sub
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
'the opened file should be the activeworkbook
call DeleteUnused
TempWkbk.Save
TempWkbk.Close savechanges:=False
Next fCtr
End If

End Sub

This goes in the same workbook as your DeleteUnused macro.
Click to expand...
 
Back
Top