Macro to Delete Blank Sheets

[rosspmm]

Hi folks,

Am being particularly dim this week :banghead: , whilst I have stuck together a short bit of code to delete worksheets, it doesn't check to ensure they are blank.

:help what I need is a bit of code that checks to see if the sheet is blank and only deletes it if it is.

It would be really nice if it would then confirm the number of sheets deleted and list in the same message/dialogue box the sheets that were not deleted as they contained data.

Any and all help is much appreciated

Ross

[mdmackillop]

Thought this sounded familiar!
http://www.vbaexpress.com/kb/getarticle.php?kb_id=396

[Killian]

Hi Ross,

Something like this then...


Sub DeleteEmptyWorkSheets()
Dim ws As Worksheet
Dim deletedsheetcount As Long
Dim strMessage As String
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If Application.CountA(ws.Cells) = 0 Then
ws.Delete
deletedsheetcount = deletedsheetcount + 1
Else
strMessage = strMessage & ws.Name & vbLf
End If
Next
Application.DisplayAlerts = True
MsgBox deletedsheetcount & " empty sheets deleted" & vbLf & _
"Remaining sheets: " & vbLf & strMessage
End Sub

[Killian]

Ahh, you beat me to it Malc - and checking for chart sheets is, of course, a very good idea which I didn't consider... :doh:
Maybe checking for shapes and other objects needs to be added?

[rosspmm]

:bow:

Thanks guys - that was very quick and both do the job marvelously

Ross

P.S. can I just add the deletedsheets count line and Msg box stuff to the code that also considers charts?