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?
Delete all blank worksheets with VBA code
Delete all blank worksheets with Kutools for Excel
Delete all blank worksheets with VBA code
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.
Delete all blank worksheets with Kutools for Excel
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.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
When you have installed Kutools for Excel, please do as the following steps:
1. Click Kutools Plus > Worksheet > 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.
Related article:
How to delete hidden worksheets in Excel?
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in