Lesson#178: Delete all blank sheets with VBA
In this article, I shall give you a VBA code that will delete all blank sheets at once.
- Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
- Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.
Here is a VBA program that will delete all blank worksheets in the active workbook:
Sub DeleteBlankWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
ws.Delete
End If
Next ws
End Sub
To use this code, open the Visual Basic Editor (VBE) by pressing Alt + F11 in Excel. Then, create a new module and paste the code into the module. To run the code, go back to Excel and press F5 or go to the VBE and click the Run button.
This code will loop through all the worksheets in the active workbook and check if the worksheet is blank by using the CountA
function. If the worksheet is blank, it will delete the worksheet.
Leave a Reply