CODEWITHSUNDEEP
excelvba
user2519971
user2519971

Reputation: 345

Delete multiple Excel Sheets in VBA

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

Upvotes: 0

Views: 2892

Answers (2)

Stavros Jon
Stavros Jon

Reputation: 1697

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.

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

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

Upvotes: 2

Related Questions