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
Bookmarks