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

(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" <(E-Mail Removed)> wrote in message news:8900C852-6FBF-490F-B940-(E-Mail 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
>

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

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 Removed)
"Sal" <(E-Mail Removed)> wrote in message
news:8900C852-6FBF-490F-B940-(E-Mail 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
>

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" <(E-Mail Removed)> wrote in message news:8900C852-6FBF-490F-B940-(E-Mail 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
> >
>

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
> (E-Mail Removed)
> "Sal" <(E-Mail Removed)> wrote in message
> news:8900C852-6FBF-490F-B940-(E-Mail 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
> >
>
>

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
>
>