Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: XL Files growing huge in size

  1. #1
    Guest

    Default

    My spreadsheet has a number section that I have built macros to add additional number section for the user if they need more space, and also to delete sections if they added a section but later dont need it. The problem is that if a section is added but then deleted, the file almost doubles in size. I was testing this by adding and deleting a section a few times and the file grew from 412k to 33meg in a short time.
    Here is my code any help would be greatly appreciated.

    Public Sub AddNum()
    On Error GoTo err_AddNum
    ActiveSheet.Unprotect ("2468")
    Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
    [np1].Copy ActiveCell.Offset(2, -2)
    ActiveSheet.PageSetup.PrintArea = Parea()
    ActiveSheet.Protect ("2468")
    Exit Sub
    err_AddNum:
    ActiveSheet.Protect ("2468")
    MsgBox (Err.Description)

    End Sub
    Public Sub DelNum()
    ActiveSheet.Unprotect ("2468")
    If Range(Cells(Rows.Count, 3).Address).End(xlUp).Row < 200 Then
    MsgBox ("Cannot Delete Last Number Sheet")
    Else
    Range(Cells(Rows.Count, 3).Address).End(xlUp).Offset(-41, 0).Rows("1:43").EntireRow.Delete Shift:=xlUp
    End If
    Range(Cells(Rows.Count, 3).Address).End(xlUp).Select
    ActiveSheet.PageSetup.PrintArea = Parea()
    err_DelNum:
    ActiveSheet.Protect ("2468")
    End Sub

  2. #2
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unfortunately I can't answer your query, but I just wanted to add that I had the same experience with a 125 sheet file when I pasted and deleted an object on each page before printing via a simple macro.

    The file got to 38Meg and the only solution was to paste each sheet into a new workbook and the file went back to its original size, but I still could not use the print macro.

    Hope this helps you salvage the file if nothing else.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is the workbook shared? If it is goto share Workbook and untick keep history.


    Regards,

    Gary Hewitt-Long

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, it is not a shared workbook.
    {I forgot to sign in when I posed this question. I posted the first question}
    Thanks, this one really has me puzzeled.
    On 2002-03-08 16:18, gplhl wrote:
    Is the workbook shared? If it is goto share Workbook and untick keep history.



  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guys

    Unfortunately this isn't that uncommon. It sounds like one of your Worksheets has become corrupt. Here is some information on how these issues can be addressed:

    Ways to reduce file size.

    1. Save the workbook as one version only. Don't save as multiple versions unless needed.

    2. Export ALL modules and Userforms to your Hard drive.

    3. Open a NEW Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then COPY the sheet to the new Workbook. Then save your new Workbook.

    4. Do the same for all Worksheets, but each time you COPY a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probably got a corrupt Worksheet. If so do step 4a

    4a. Delete the copied sheet. In the original Workbook, go to the last row of data on the sheet (do not use Edit>Go to-Special>Last cell, see "Find the last Row, Column or Cell" here: http://www.ozgrid.com/VBA/ExcelRanges.htm). Select the entire last row and holding down your Ctrl and Shift key push the Down arrow. This should take you to row 65536. Now go to Edit>Clear>All. Do the same for you Columns, but push the Right arrow! Now Save. Now push Ctrl+A and copy it's content to a New sheet in the Workbook and try again.

    5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.

    6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.

    7 Use Dynamic ranges where applicable as these can often be used to replace references like entire Columns and restrict the referenced range to only what is needed. http://www.ozgrid.com/Excel/DynamicRanges.htm

    Here are some interesting links. Some may apply ?

    http://support.microsoft.com/support.../Q186/3/69.ASP

    http://archive.baarns.com/excel/develop/vbaperfm.asp

    I hope this helps. Good Luck


    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training
    If it's Excel, then it's us!

    [ This Message was edited by: Dave Hawley on 2002-03-08 19:24 ]

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to add to all the others good advise

    have alook here;
    http://support.microsoft.com/search/...;en-us;Q299372


    Ivan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •