PDA

View Full Version : Delete Empty Sheets



AZIQN
04-07-2009, 10:35 AM
Hi All, I'd like the macro to delete all the blank sheets of the workbook, or each sheet in which cell "A1" is empty (whichever way is easier to code). I tried the following code, but it is not working correctly. Any help would be appreciated. Thanks!

With ActiveWorkbook
For Each Worksheet In ThisWorkbook.Worksheets
If .Cells(1, "A").Value = "" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End If
Next Worksheet
End With

Bob Phillips
04-07-2009, 11:27 AM
With ActiveWorkbook

For Each sh In ThisWorkbook.Worksheets

If sh.Cells(1, "A").Value = "" Then

Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End With

AZIQN
04-07-2009, 11:43 AM
Thanks for your quick reply Bob. However, when I run the code now I am getting an error stating that the workbook must contain at least one sheet -- but not all sheets are blank so not all of them should be deleted. Any idea whats going wrong? Thanks.

AZIQN
04-07-2009, 12:08 PM
Nevermind the previous error. I found another solution using the count function rather than checking the value of cell A1. This works without error:
For Each sh In Sheets
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh

Thanks again for your help.

Bob Phillips
04-07-2009, 12:44 PM
That gives the same problem surely? If all sheets could be empty then try this



With ActiveWorkbook

For Each sh In ThisWorkbook.Worksheets

If sh.Cells(1, "A").Value = "" And ThisWorkbook.Worksheets .Count > 1 Then

Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End If
Next sh
End With

AZIQN
04-07-2009, 03:27 PM
Surprisingly, the code I posted with the count function does not produce an error...I'm not sure why. In my workbook, its impossible for all the sheets to be empty, so I'm not sure why I was getting that error.

Bob Phillips
04-08-2009, 12:02 AM
Probably because you originally stated ... delete all the blank sheets of the workbook, or each sheet in which cell "A1" is empty (whichever way is easier ... I used thge cell A1, so you must have sheets that are NOT empty, but A1 is.

AZIQN
04-09-2009, 07:15 AM
That's not the case either...all the sheets that are not empty contain a date value in cell A1. Either way, we found a solution that will work...I appreciate your help. Thanks!

Benzadeus
04-09-2009, 09:26 AM
But it is clear that the macro won't delete the sheet when A1 is filled, that's weird.