Delete Sheets based on condition

[Zakkaroo]

I need to write some VBA that loops through all existing sheets (not including the first two) and deletes any sheet that matches a certain condition.

In each sheet where Cell B2 OR B3 OR B4 OR B5 OR B6 is empty. That sheet can be deleted.




I've given it a go, but there are a few issues with it:

1. It doesn't delete all the sheets. I think this is because, when I delete Sheet #4. Sheet #5 becomes sheet #4, but the macro is then running on Sheet #5? Not 100% sure, but you have to run it multiple times for it to delete all sheets that meet the criteria
2. When it runs on the last sheet you get a VBA error
3. I would like it to run when the user tries to close the workbook, but not 100% sure where to put the code

My VBA Skills aren't that great .... so here we go

Sub SaveAndClose()
Dim i As Integer
For i = 3 To Worksheets.Count
        Worksheets(i).Activate
       If ActiveWorkbook.Worksheets(i).Range("B2").Value = "" Or ActiveWorkbook.Worksheets(i).Range("B3").Value = "" Or ActiveWorkbook.Worksheets(i).Range("B4").Value = "" Or ActiveWorkbook.Worksheets(i).Range("B5").Value = "" Or ActiveWorkbook.Worksheets(i).Range("B6").Value = "" Then
        End If
Next i
ActiveWorkbook.Save
End Sub

 

[JackDanIce]

Try:

Sub DeleteMySheets ()
Dim i as Long
For i = Worksheets.Count to 3 Step -1
  With sheets(i)
     IF Len(.Range("B2")) = 0 OR Len(.Range("B3")) = 0 OR Len(.Range("B4")) = 0 OR Len(.Range("B5")) = 0 OR Len(.Range("B6")) = 0 Then .Delete
  End With
Next i
ActiveWorkbook.Save
End Sub

 

[JackDanIce]

You probably want to include this line near the start of the macro to turn error alerts off:

Application.DisplayAlerts = False

 

[VoG]

You need to loop backwards. Try

Sub SaveAndClose()
Dim i As Integer
Application.DisplayAlerts = False
For i = Worksheets.Count To 3 Step -1
       If Worksheets(i).Range("B2").Value = "" Or Worksheets(i).Range("B3").Value = "" Or Worksheets(i).Range("B4").Value = "" _
       Or Worksheets(i).Range("B5").Value = "" Or Worksheets(i).Range("B6").Value = "" Then Worksheets(i).Delete
Next i
Application.DisplayAlerts = True
ActiveWorkbook.Save
End Sub

 

[Zakkaroo]

Thank you both for the speedy replies. I will get to try this out tomorrow most likely as i've gotta rush out now. But both responses seem to make more sense than my original approach.

One final question. If i wanted this to run when the user closed the workbook, would 'beforeClose' be the best place to stick it?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 

[JackDanIce]

Yes

 

[GTO]

Hi there,

Just a suggestion, but if you fire it from BeforeClose, the wb will need to be re-saved of course. I would have the code first check to see if the file is saved; if true, then delete sheets and re-save.

Mark

 

[Eskypades]

Just wanted to add my thanks for this simple code that worked beautifully for what I needed.