How to find and delete sheets with specific names using VBA?

To find and delete sheets with specific names using VBA (Visual Basic for Applications) in Excel, you can create a macro that loops through all sheets in the workbook, checks their names against your specified criteria, and deletes the matching ones. Here’s a step-by-step guide on how to do this:

Sub DeleteSheetsByName()
    Dim ws As Worksheet
    Dim sheetNamesToDelete As Collection
    Dim sheetName As Variant
    Dim response As VbMsgBoxResult
    
    ' Initialize the collection with sheet names you want to delete
    Set sheetNamesToDelete = New Collection
    sheetNamesToDelete.Add "Sheet1" ' Replace with your sheet name
    sheetNamesToDelete.Add "Sheet2" ' Add more as needed
    
    ' Confirm deletion with the user
    response = MsgBox("This will delete all sheets listed in the macro. Do you want to continue?", vbYesNo, "Delete Sheets")
    
    If response = vbNo Then Exit Sub
    
    Application.DisplayAlerts = False ' Disable alerts temporarily
    
    ' Loop through each sheet in the workbook
    For Each sheetName In sheetNamesToDelete
        On Error Resume Next ' Continue if there is an error (e.g., sheet does not exist)
        Set ws = ThisWorkbook.Sheets(sheetName)
        If Not ws Is Nothing Then
            ws.Delete ' Delete the sheet
            Set ws = Nothing
        End If
        On Error GoTo 0
    Next sheetName
    
    Application.DisplayAlerts = True ' Re-enable alerts
End Sub

Open Excel and Access the VBA Editor:

Open your Excel workbook.
Press `ALT + F11` to open the VBA editor.

Insert a New Module:

In the VBA editor, go to the menu bar and click on `Insert > Module`. This will create a new module to write your macro in.

Write the Macro:

Copy and paste the following VBA code into the module window:

Customize the Sheet Names:

Replace `Sheet1`, `Sheet2`, etc., with the actual names of the sheets you want to delete. You can add or remove entries in the `sheetNamesToDelete` collection as needed.

Run the Macro:

You can run the macro by pressing `F5` while the code is selected, or by closing the VBA editor, going back to Excel, and running it from the `Developer` tab (`Developer > Macros`, then select `DeleteSheetsByName` and click `Run`).

Important:

Be careful when using this macro, as it will permanently delete sheets without sending them to a recycle bin. Always make sure to have backups of your data before running destructive operations like this.
This macro disables alerts temporarily to avoid the prompt that asks for confirmation to delete a sheet. If you prefer to have this prompt, you can remove the lines that disable and re-enable alerts.

facebooklinkedin