How to delete a sheet by specifying the sheet name VBA(Macro)Excel(Excel)

As the number of unneeded sheets grows, browsing becomes worse, and Excel itself becomes slower. In such cases, you can use VBA to automatically delete unnecessary sheets. In this article, we will show you how to delete sheets at high speed using VBA.

If you want to delete only the contents of a sheetDelete data from a sheet Excel(Excel) Macro(VBA).

To delete a sheet by specifying its name

We will start with the simplest method, which is to write the name of the sheet to be deleted directly in the VBA description.

Sub sheetdel()

    Sheets("Delete sheet").Delete Delete sheets stored in 'tstsh
    
End Sub

If you run this VBA

You will get an alert like this, and if you click "Delete

The sheet has been deleted.

To remove an alert message (warning) without it appearing

If you do not mind alert messages (warnings) appearing, this is fine, but if you do not want alert messages (warnings) to appear, you can set "Application.DisplayAlerts" to "False" to prevent the messages from appearing. The specific method is as follows.

Sub sheetdel()

    Application.DisplayAlerts = False Do not display alerts during processing after
    Sheets("Delete sheet").Delete
    Application.DisplayAlerts = True ' Alerts during subsequent processing
    
End Sub

If you run this VBA

No alerts were displayed and the sheet was deleted in an instant.

To delete a sheet by specifying its name

In the above cases, the sheet names are written directly in the code, which is very unmaintainable for later use. Therefore, let's change the code to delete a sheet by specifying the sheet name.

There are many ways to specify sheets, but in this case we will enter the name of the sheet we want to delete in the "A1" cell of Excel, and the code will delete the sheet whose name is the same as the value of the cell.

I typed the name of the sheet I wanted to delete, "Delete Sheet," in the "A1" cell of the "Sheet1" sheet.

Sub sheetdel()

    Dim shname As String
    Dim tgtsh As Worksheet

    shname = Sheets("sheet1").Range("A1").Value 'Store the contents of cell "A1" in shname
    Set tstsh = Sheets(shname)     'Store the sheets to be deleted in tstsh
    
    Application.DisplayAlerts = False Do not display alerts during processing after
    tstsh.DeleteDelete 'tstsh
    Application.DisplayAlerts = True ' Alerts during subsequent processing
    
End Sub

If you run this VBA

Thus, the "delete sheet" disappeared in an instant.

Conclusion

In this case, we have chosen to designate the sheet by entering the "A1" cell, but there are many other ways to designate a sheet. There are other ways to designate a sheet, such as naming a cell situation instead of its coordinates, selecting from a pull-down menu, or using a message box to specify a sheet each time.

In either case, specifying a sheet name that does not exist will result in an error, but we will explain how to avoid the error in another article.

Find more convenient ways to combine various means.

I hope this article has been helpful to you.