2 Questions

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
1. Do empty sheets in workbooks take up much storage space?

2. If so, I would like to write a macro to search through directorys and delete empty worksheets in workbooks.

Does anyone have any ideas?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
A quick test with two files, one with 3 empty sheets and one with almost 200 suggests that each blank worksheet is about 0.5 KB worth of space, assuming linear growth. So, 200 blank sheets would add a little over 100KB to your file size. Probably not enough to cause concern.
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi t,

They don't really use up much space (especially when you consider the size of modern hard-drives).

If you did want to delete them then you could use something like this:
Code:
Sub test()
    Call DeleteEmpties(ThisWorkbook)
End Sub

Sub DeleteEmpties(wbk As Workbook)
'remove all empty sheets from the workbook
    Dim ws As Worksheet, bNonEmpty As Boolean
    
    Application.DisplayAlerts = False
    For Each ws In wbk.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            If ws.Visible = xlSheetVisible Then
                If WorksheetFunction.CountA(ws.Cells) > 0 Then
                    bNonEmpty = True
                Else
                    ws.Delete
                End If
            End If
        End If
    Next ws
    If bNonEmpty And Not WorksheetFunction.CountA(Cells) > 0 Then ActiveSheet.Delete

    Application.DisplayAlerts = True

End Sub
Just amend the Test sub so that you are using Dir or Filesearch to loop through the workbooks concerned.

HTH
 

Forum statistics

Threads
1,147,691
Messages
5,742,661
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top