Delete Empty Worksheets with Excel VBA

Delete Empty Worksheets VBA Code:  

Sub DeleteEmptyWorksheets()
    Dim ws As Worksheet
    Dim wsCount As Integer
    Dim i As Integer
    Dim lastRow As Long, lastCol As Long
    Dim wsToDelete As Collection
    Dim wsName As String
    Dim response As VbMsgBoxResult
    ' Initialize a collection to store empty worksheet names
    Set wsToDelete = New Collection
    ' Count the total number of worksheets
    wsCount = ThisWorkbook.Worksheets.Count
    ' Prevent deletion if only one worksheet remains
    If wsCount = 1 Then
        MsgBox "Cannot delete the only worksheet in the workbook!", vbExclamation, "Delete Empty Worksheets"
        Exit Sub
    End If
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Find the last used row and column
        lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        ' If no used range is found, the worksheet is empty
        If lastRow = 1 And lastCol = 1 Then
            If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
                ' Store the worksheet name in the collection
                wsToDelete.Add ws.Name
            End If
        End If
    Next ws
    ' Confirm deletion if there are empty worksheets
    If wsToDelete.Count > 0 Then
        wsName = "The following empty worksheets will be deleted:" & vbNewLine & vbNewLine
        For i = 1 To wsToDelete.Count
            wsName = wsName & wsToDelete(i) & vbNewLine
        Next i
        response = MsgBox(wsName & vbNewLine & "Do you want to proceed?", vbYesNo + vbQuestion, "Confirm Deletion")
        If response = vbYes Then
            Application.DisplayAlerts = False
            For i = 1 To wsToDelete.Count
                ThisWorkbook.Worksheets(wsToDelete(i)).Delete
            Next i
            Application.DisplayAlerts = True
            MsgBox "Empty worksheets deleted successfully.", vbInformation, "Delete Empty Worksheets"
        Else
            MsgBox "No worksheets were deleted.", vbInformation, "Delete Empty Worksheets"
        End If
    Else
        MsgBox "No empty worksheets found.", vbInformation, "Delete Empty Worksheets"
    End If
End Sub

Detailed Explanation

This VBA macro scans through all worksheets in the active workbook and deletes those that are empty. Here’s a step-by-step breakdown of the code:

1. Initialize Variables
   • ws: Used to iterate through worksheets.
   • wsCount: Stores the total number of worksheets.
   • i: Loop counter.
   • lastRow and lastCol: Identify the last used row and column.
   • wsToDelete: A collection to store the names of empty worksheets.
   • wsName: Stores worksheet names for confirmation.
   • response: Captures user response in the confirmation message.
2. Check If Only One Worksheet Exists
   • If the workbook has only one worksheet, the macro displays a message and exits because deleting the last worksheet is not allowed.
3. Loop Through All Worksheets
   • The macro examines each worksheet to determine if it is empty.
   • ws.Cells.Find(« * », SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row: Finds the last used row.
   • ws.Cells.Find(« * », SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column: Finds the last used column.
   • If both lastRow and lastCol are 1 and there are no non-empty cells (Application.WorksheetFunction.CountA(ws.Cells) = 0), the worksheet is considered empty.
   • The empty worksheet’s name is stored in the wsToDelete collection.
4. Confirm Deletion with the User
   • If at least one empty worksheet is found, a message box lists the worksheets and asks the user for confirmation before proceeding.
5. Delete Empty Worksheets
   • If the user confirms, the macro:
     • Temporarily disables Application.DisplayAlerts to suppress deletion warnings.
     • Deletes each worksheet in the wsToDelete collection.
     • Re-enables Application.DisplayAlerts.
     • Displays a confirmation message.
6. Handle Cases Where No Empty Worksheets Are Found
   • If no empty worksheets exist, the macro informs the user and exits.

Why This Code Is Effective?

✔ Prevents Deleting the Last Worksheet: Ensures that at least one worksheet remains.
✔ Accurate Detection of Empty Worksheets: Uses .Find and CountA functions to confirm emptiness.
✔ User Confirmation Before Deletion: Prevents accidental deletions.
✔ Batch Deletion Using a Collection: Improves efficiency by first identifying all empty sheets before deletion.
✔ Handles Alerts Properly: Prevents unnecessary warnings during deletion.