How to delete all empty sheets in a workbook using VBA?

To delete all empty sheets in an Excel workbook using VBA, you can use the following code. This macro will loop through each sheet in the workbook, check if it’s empty, and then delete it if it is. An empty sheet is considered one without any data in its used range.

Here is a sample VBA code to accomplish this task:

Sub DeleteEmptySheets()
    Dim ws As Worksheet
    Dim n As Integer
    Dim CallDelete As Boolean
    
    Application.ScreenUpdating = False ' Turn off screen updating for performance
    Application.DisplayAlerts = False ' Turn off alert messages
    
    For n = ThisWorkbook.Worksheets.Count To 1 Step -1
        Set ws = ThisWorkbook.Worksheets(n)
        If Application.WorksheetFunction.CountA(ws.UsedRange) = 0 Then
            ws.Delete
        End If
    Next n
    
    Application.DisplayAlerts = True ' Turn on alert messages
    Application.ScreenUpdating = True ' Turn on screen updating
End Sub

Explanation:

ScreenUpdating and DisplayAlerts: These are turned off at the beginning of the macro to improve performance and prevent Excel from asking user confirmation for each sheet deletion.
Loop through each sheet: The `For` loop iterates over all the sheets in the workbook in reverse order. This is important because removing sheets while iterating forwards could cause errors (as the collection is being changed while iterating over it).
Check if the sheet is empty: This is done using `CountA(ws.UsedRange)`. This function counts all non-empty cells in the sheet’s used range. If it returns zero, the sheet is considered empty.
Delete the sheet: The sheet is deleted if it’s empty.
Restore Settings: At the end, screen updating and alerts are turned back on.

To use this code:

Press `Alt + F11` to open the VBA editor in Excel.
Go to `Insert > Module` to create a new module.
Paste the above code into the module.
Close the VBA editor and run the macro `DeleteEmptySheets` from the macro menu (`Alt + F8`) within Excel.

Always make sure to save your workbook before running the macro, since this operation is irreversible in its current form and you might lose data if the macro deletes sheets that you did not intend to delete.

facebooklinkedin