How to delete all the blank worksheets in Excel?
If you have a workbook which has lots of worksheets, meanwhile, there are some blank worksheets in it. And now you want to delete the blank worksheets to save or archive the file. How could you search for the blank sheets from multiple worksheets and delete them automatically?
It is troublesome for you to check for the blank worksheets and remove them one by one, but with the following VBA code, you can easily delete all blank worksheets at once.
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module window.
VBA: delete all blank worksheets
Sub DeleteBlankWorksheets() 'Update 20130830 Dim Ws As Worksheet On Error Resume Next Application.ScreenUpdating = False Application.DisplayAlerts = False For Each Ws In Application.Worksheets If Application.WorksheetFunction.CountA(Ws.UsedRange) = 0 Then Ws.Delete End If Next Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
3. Then press F5 key, and all the blank worksheets are deleted at once.
If you are not familiar with the VBA code, I can introduce you an easy tool- Kutools for Excel, with its Delete Blank Worksheets function, you can remove all empty worksheets with one click.
When you have installed Kutools for Excel, please do as the following steps:
1. Click Enterprise > Worksheet Tools > Delete Blank Worksheets, see screenshot:
2. And a prompt box will appear to remind you whether you want to delete all blank worksheets or not, see screenshot:
3. Then click Yes, and the blank worksheets have been deleted successfully. See screenshots:
If you want to know more about this feature, please click Delete Blank Worksheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!