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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.