Is it possible to use a macro to delete all the worksheets inmy workboo
that do not contain any data?

Everytime i try to come up with something i end up losing somethin
important so would be grateful for any hel

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Ian,

Try This:

It looks for the number of cells in the sheet's used range, and (when blank)
that is equal to 1. If the one cell has nothing in it, then the sheet is
empty and can be deleted.

Alex J

Sub DeleteBlankWS()
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
cellcount = ws.UsedRange.Cells.Count
If cellcount = 1 Then
If ThisWorkbook.Worksheets.Count 1 Then
If ws.UsedRange.Value = "" Then
Rtnn = MsgBox("Deleting Worksheet ["
& ws.Name & "]", vbExclamation + vbOKCancel, "Delete Blank Worksheets")
If Rtnn = vbOK Then ws.Delete
End If
Else
Rtnn = MsgBox("Cannot Delete Last Worksheet
in Workbook", vbExclamation + vbOK, "Delete Blank Worksheets")
End If
End If
Next ws
Application.DisplayAlerts = True
End Sub




"ian123" wrote in message
...

Is it possible to use a macro to delete all the worksheets inmy workbook
that do not contain any data?

Everytime i try to come up with something i end up losing something
important so would be grateful for any help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

A slightly different alternative:

Public Sub DeleteBlankWS()
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
For Each ws In ActiveWorkbook.Worksheets
If Application.CountA(ws.Cells) = 0 Then
If ActiveWorkbook.Worksheets.Count 1 Then
ws.Delete
Else
MsgBox "Could not delete last worksheet."
End If
End If
Next ws
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

In article ,
"Alex J" wrote:

Ian,

Try This:

It looks for the number of cells in the sheet's used range, and (when blank)
that is equal to 1. If the one cell has nothing in it, then the sheet is
empty and can be deleted.

Alex J

Sub DeleteBlankWS()
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
cellcount = ws.UsedRange.Cells.Count
If cellcount = 1 Then
If ThisWorkbook.Worksheets.Count 1 Then
If ws.UsedRange.Value = "" Then
Rtnn = MsgBox("Deleting Worksheet ["
& ws.Name & "]", vbExclamation + vbOKCancel, "Delete Blank Worksheets")
If Rtnn = vbOK Then ws.Delete
End If
Else
Rtnn = MsgBox("Cannot Delete Last Worksheet
in Workbook", vbExclamation + vbOK, "Delete Blank Worksheets")
End If
End If
Next ws
Application.DisplayAlerts = True
End Sub




"ian123" wrote in message
...

Is it possible to use a macro to delete all the worksheets inmy workbook
that do not contain any data?

Everytime i try to come up with something i end up losing something
important so would be grateful for any help


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements