How to Remove Empty Sheets from a Workbooks?

Working with blank sheets that have no purpose in your workbook can be frustrating if you've ever worked with Excel files that contain several sheets. Your data may become cluttered by these blank sheets, making it more difficult to traverse and perplexing for other users who interact with the file. It will meet your needs whether you're a novice or an advanced Excel user.

Make sure you are familiar with the fundamentals of Microsoft Excel and how to move between worksheets before we begin. For practise, we suggest having a sample workbook with a few blank sheets in addition to the tutorial. Now let's get started and effectively remove those empty sheets from your Excel workbooks to clean them. You'll be astounded at how quickly you can clean up your data and enhance your Excel workflow if you carefully follow each step! Let's start now!

Remove Empty Sheets from a Workbooks

Here we will first insert a VBA module and then run it to complete the task. So let us see a simple process to learn how you can remove empty sheets from a workbook in Excel.

Step 1

Consider an Excel workbook where you have multiple worksheets with some empty sheets.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub DeletEmptySheets()
   Dim xWs As Worksheet
   Dim xAlert As Boolean
   Dim xCount As Long
   xAlert = Application.DisplayAlerts
   Application.DisplayAlerts = False
   If MsgBox("Are you sure to remove all empty sheets?", vbYesNo, "Remove Empty Sheets") = vbNo Then Exit Sub
   For Each xWs In ActiveWorkbook.Worksheets
      If (Application.CountA(xWs.Cells) = 0) And _
         (xWs.Shapes.Count = 0) Then
         xWs.Delete
         xCount = xCount + 1
      End If
   Next
   MsgBox "You have removed " & xCount & " empty sheet(s)", , "Remove Empty Sheets"
   Application.DisplayAlerts = xAlert
End Sub

Step 3

Then click F5 to run the module. Then click OK in the conformation box. Then you will see that the empty sheet has been removed.

F5 > OK.

This is how you can remove empty sheets from a workbook in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove empty sheets from a workbook in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 07-Sep-2023

43 Views

Kickstart Your Career

Get certified by completing the course

Get Started