Results 1 to 10 of 10

Adding and deleting empty sheets increases file size.

Threaded View

  1. #1
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,692

    Adding and deleting empty sheets increases file size.

    Adding and deleting empty sheets increases file size.
    I saw some code on one of these sites, and unfortunately I don't know where and who's code it is so I cant thank him/her, and I wanted to compare it to some other methods of deleting sheets.
    Following is the Function and macro I am talking about.
    The timers were added by me to give me an idea for comparing purposes.
    When I rune these macros several times, the file size goes from an original of 24 KB (See attached) to 600+ KB
    Nothing is added to the 3 sheets but I checked that just in case.
    What is the culprit that increases the file size?
    All these codes are in the attached workbook
    Sub DeleteSheets()
    Dim t
        Dim vaSheets As Variant
        vaSheets = gvaSheets()
        t = Timer
        If Not IsEmpty(vaSheets) Then
            With Application
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .DisplayAlerts = False
                    .StatusBar = "Deleting previously added worksheets . . ."
                        On Error Resume Next
                            ThisWorkbook.Worksheets(vaSheets).Delete
                        On Error GoTo 0
                    .StatusBar = False
                .DisplayAlerts = True
                .Calculation = xlCalculationAutomatic
                .ScreenUpdating = True
            End With
        End If
    Range("L7").Value = "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
    End Sub
    
    
    '=========================================================================================
    '=========================================================================================
    
    
    Function gvaSheets() As Variant
        Const iMAX_SHEETS As Integer = 997
        Dim vaSheets            As Variant
        Dim iSheetNo            As Integer
        Dim wks                 As Worksheet
        ReDim vaSheets(1 To iMAX_SHEETS)
        iSheetNo = 0
        For Each wks In ThisWorkbook.Worksheets
        'I changed the following three lines to fit the setup of my testing Workbook (see attached)
            If wks.Name <> "Sheet1" And _
               wks.Name <> "Sheet2" And _
               wks.Name <> "Sheet3" Then
                iSheetNo = iSheetNo + 1
                vaSheets(iSheetNo) = wks.Name
            End If
        Next wks
        If iSheetNo > 3 Then
              ReDim Preserve vaSheets(1 To iSheetNo)
        Else: vaSheets = Empty
              MsgBox "The workbook does not contain any student worksheets " & _
                     "to export/delete", vbExclamation, " No worksheets located"
        End If
        gvaSheets = vaSheets
    End Function
    I use the following code to add sheets.
    Sub Add_A_Bunch_Of_Sheets()
    If ActiveWorkbook.Sheets.Count > 3 Then MsgBox "Sheets were added previously.": Exit Sub
    Application.ScreenUpdating = False
    Dim i As Long
    Dim t
    t = Timer
        For i = 4 To 1000
            Sheets.Add(, Sheets(Sheets.Count)).Name = "Sheet" & i
        Next i
    Sheets("Sheet1").Select
    Range("L2").Value = "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
    Application.ScreenUpdating = True
    End Sub
    The following three macros also are used to delete back to 3 sheets.
    Sub Delete_Added_Sheets_With_Loop()
    Dim i As Long
    Dim t
    t = Timer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        For i = Sheets.Count To 4 Step -1
            Sheets(i).Delete
        Next i
    Range("L9").Value = "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    Sub Delete_Added_Sheets_By_Selecting_Them()
    If ActiveWorkbook.Sheets.Count = 3 Then MsgBox "No Sheets to delete!": Exit Sub
    Dim i As Long
    Dim t
    t = Timer
    Application.ScreenUpdating = False
    Sheets("Sheet4").Select
    For i = 5 To ThisWorkbook.Sheets.Count
        Sheets("Sheet" & i).Select Replace:=False
    Next i
    Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets("Sheet1").Select
    Range("L11").Value = "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
    Application.ScreenUpdating = True
    End Sub
    Sub Delete_Sheets_With_Do_Loop()
    If ActiveWorkbook.Sheets.Count = 3 Then MsgBox "No Sheets to delete!": Exit Sub
    Dim t
    t = Timer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        Do While Worksheets.Count > 3
            Worksheets(Worksheets.Count).Delete
        Loop
    Application.DisplayAlerts = True
    Range("L13").Value = "This macro took " & Format(Round(Timer - t, 2), "00:00:00.00") & " seconds to run."
    
    Application.ScreenUpdating = True
    End Sub

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel file size increases on its own
    By umbata in forum Excel General
    Replies: 2
    Last Post: 04-02-2014, 07:05 AM
  2. Replies: 5
    Last Post: 09-13-2012, 11:50 AM
  3. File size increases significantly with Macros
    By tdyl1969 in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 08:02 PM
  4. Excel file size increases when editing same info
    By Ron Bevan in forum Excel General
    Replies: 3
    Last Post: 07-25-2006, 11:00 AM
  5. [SOLVED] File size increases with each save
    By Garbunkel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-05-2005, 06:05 PM
  6. Why autoformat increases file size?
    By kdw in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 07:06 PM
  7. Importing .txt data files increases .xls file size
    By BrianJ in forum Excel General
    Replies: 1
    Last Post: 01-29-2005, 10:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1