2 Questions

[tmischler]

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?

 

[Oaktree]

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.

 

[tmischler]

thanks

 

[Richie(UK)]

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:

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

 

[tmischler]

thanx