Delete multiple Excel Sheets in VBA

Question

I am using an excel Workbook for programtical generation. Once the workbook is created few of the sheets are having required data and few are blank with default templates only.

I need to delete all sheets having default templates (means no data). I can check specific cell to identify this however need to know how to check for all sheets and then delete sheets one by one.

I am having this piece of code:

Sub TestCellA1()
  'Test if the value is cell D22 is blank/empty
  If IsEmpty(Range("D22").Value) = True Then
    MsgBox "Cell A1 is empty"
  End If
End Sub

Michał Turczyn · Accepted Answer · 2019-05-13 05:51:34Z

2

Try this:

Sub DeleteEmptySheets()
    Dim i As Long, ws As Worksheet
    ' we don't want alerts about confirmation of deleting of worksheet
    Application.DisplayAlerts = False
    For i = Worksheets.Count To 1 Step -1
        Set ws = Worksheets(i)
        ' check if cell D22 is empty
        If IsEmpty(ws.Range("D22")) Then
            Sheets(i).Delete
        End If
    Next
    ' turn alerts back on
    Application.DisplayAlerts = True
End Sub

answered May 13, 2019 at 5:51

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stavros Jon · Accepted Answer · 2019-05-13 09:39:27Z

2

An alternative implementation using For-Each:

Sub deleteSheets()
Dim wb As Workbook
Dim sht As Worksheet
Set wb = Workbooks("Name of your Workbook")
'Set wb = ThisWorkbook   You can use this if the code is in the workbook you want to work with
Application.DisplayAlerts = False 'skip the warning message, the sheets will be deleted without confirmation by the user.
For Each sht In wb.Worksheets
    If IsEmpty(sht.Range("D22"))  And wb.Worksheets.Count > 1 then
        sht.Delete
    End If
Next sht
Application.DisplayAlerts = True
End Sub

This mainly serves as a demonstration pf how you can easily loop through worksheets.

As suggested in the comments below by @Darren Bartrup-Cook , the logic according to which the sheets are deleted can and should be modified to not only suit your purposes but to also include safeguards.

Making sure there's always at least one worksheet in the workbook is one of them. This can be ensured in a multitude of ways. I updated my answer to implement one these.

edited May 13, 2019 at 9:39

answered May 13, 2019 at 9:09

Stavros Jon

1,7072 gold badges10 silver badges18 bronze badges

2 Comments

Darren Bartrup-Cook Over a year ago

Maybe also check visible worksheets count > 1 before deleting just in case it attempts to delete the last sheet.

Nacorid Over a year ago

You should also mention that using For Each to loop through a collection you are changing (e.g. deleting or adding items) should be avoided, even if you know what you are doing.

Collectives™ on Stack Overflow

Delete multiple Excel Sheets in VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related