PDA

View Full Version : [SOLVED] Macro to Delete Blank Sheets



rosspmm
06-30-2005, 03:13 AM
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
06-30-2005, 03:58 AM
Thought this sounded familiar!
http://www.vbaexpress.com/kb/getarticle.php?kb_id=396

Killian
06-30-2005, 04:07 AM
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
06-30-2005, 04:12 AM
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
06-30-2005, 04:17 AM
: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?