| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does File Size increase with reducing spreadsheet information?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet information?
This may help.
Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
Thank you very much for your suggestions
Eric "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
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 "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
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. Eric wrote: 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 "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
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 "Dave Peterson" wrote: 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. Eric wrote: 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 "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
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. Eric wrote: 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 "Dave Peterson" wrote: 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. Eric wrote: 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 "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why does File Size increase with reducing spreadsheet informat
Thank you very much for your suggestions
Eric "Dave Peterson" wrote: 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. Eric wrote: 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 "Dave Peterson" wrote: 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. Eric wrote: 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 "Dave Peterson" wrote: This may help. Debra Dalgleish does share some techniques for resetting the usedrange: http://contextures.com/xlfaqApp.html#Unused Eric wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reducing size of a file with no data | Excel Discussion (Misc queries) | |||
| Reducing Excel File Size | Excel Discussion (Misc queries) | |||
| reducing file size | Excel Discussion (Misc queries) | |||
| Reducing file size | Excel Discussion (Misc queries) | |||
| Reducing the size of an xls file with pivot charts | Charts and Charting in Excel | |||
Linear Mode
