Delete Sheets Without Contents

[Sal]

I would like to get help to improve the macro below so that if Sheet2 or
Sheet3 or both, have no contents in their worksheets they will get deleted.
On the other hand if Sheet2 or Sheet3, or both have contents they will stay.
Here is the code I have right now. Thank you for your help.

Sub Deletesheetswithoutcontents()
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete
Application.DisplayAlerts = True

End Sub

 

[Rick Rothstein]

(Untested) This should do what you want...

Sub DeleteEmptySheets2or3()
Application.DisplayAlerts = False
If Sheets("Sheet2").Cells.Find("*") Is Nothing Then Sheets("Sheet2").Delete
If Sheets("Sheet3").Cells.Find("*") Is Nothing Then Sheets("Sheet3").Delete
Application.DisplayAlerts = True
End Sub

 

[OssieMac]

Helo Sal,

I like Rick's method of testing for data also but I would also test for the
existance of the worksheets otherwise if the code is run again after the
sheets are deleted then it will error out so have posted another option
including a different method of testing for existance of data.

The Else with msgbox if sheet contains data is optional.

Note that the space and underscore at the end of a line is a line break in
an otherwise single line of code.

Sub DeleteSheetsWithoutContents()

Dim ws As Worksheet

Application.DisplayAlerts = False

On Error Resume Next
Set ws = Sheets("Sheet2")
On Error GoTo 0

If Not ws Is Nothing Then 'Sheet exists
If WorksheetFunction. _
CountA(ws.UsedRange) = 0 Then
ws.Delete
Else
MsgBox ws.Name & " contains data"
End If
End If

Set ws = Nothing

On Error Resume Next
Set ws = Sheets("Sheet3")
On Error GoTo 0

If Not ws Is Nothing Then 'Sheet exists
If WorksheetFunction. _
CountA(ws.UsedRange) = 0 Then
ws.Delete
Else
MsgBox ws.Name & " contains data"
End If
End If

Application.DisplayAlerts = True

End Sub

 

[Don Guillett]

Sub testshts()
Application.DisplayAlerts = False
On Error Resume Next
For i = Worksheets.Count To 2 Step -1
If Sheets(i).Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Address = " " Then
'MsgBox Sheets(i).Name
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True
End Sub

 

[Sal]

Thank you Sir. This is very helpful.

(Untested) This should do what you want...

Sub DeleteEmptySheets2or3()
Application.DisplayAlerts = False
If Sheets("Sheet2").Cells.Find("*") Is Nothing Then Sheets("Sheet2").Delete
If Sheets("Sheet3").Cells.Find("*") Is Nothing Then Sheets("Sheet3").Delete
Application.DisplayAlerts = True
End Sub

 

[Sal]

Cool code. It works well. I am grateful for your help. Thank you.

Sub testshts()
Application.DisplayAlerts = False
On Error Resume Next
For i = Worksheets.Count To 2 Step -1
If Sheets(i).Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Address = " " Then
'MsgBox Sheets(i).Name
Sheets(i).Delete
End If
Next i
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I would like to get help to improve the macro below so that if Sheet2 or
Sheet3 or both, have no contents in their worksheets they will get
deleted.
On the other hand if Sheet2 or Sheet3, or both have contents they will
stay.
Here is the code I have right now. Thank you for your help.

Sub Deletesheetswithoutcontents()
Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete
Application.DisplayAlerts = True

End Sub

 

[Sal]

You make some interesting points that are good to think about. I appreciate
your assistance. Thank you friend. The macro works very nicely.