Delete Sheets Without Contents
 

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

Delete Sheets Without Contents
 

(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

--
Rick (MVP - Excel)


"Sal" wrote in message ...
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

Delete Sheets Without Contents
 

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


--
Regards,

OssieMac

Delete Sheets Without Contents
 

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

"Sal" wrote in message
...
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

Delete Sheets Without Contents
 

Thank you Sir. This is very helpful.

"Rick Rothstein" wrote:

(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

--
Rick (MVP - Excel)


"Sal" wrote in message ...
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

Delete Sheets Without Contents
 

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

"Don Guillett" wrote:

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

"Sal" wrote in message
...
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

Delete Sheets Without Contents
 

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

"OssieMac" wrote:

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


--
Regards,

OssieMac