Delete all blank worksheets?

  • Thread starter Thread starter ian123
  • Start date Start date
I

ian123

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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
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

Alex J said:
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 said:
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


------------------------------------------------



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