如何使用VBA删除所有的空工作表

如何使用VBA删除所有的空工作表

解答：可以分两种情形来对待。

(一)如果您想删除同一工作簿中的所有空工作表，可以使用下面的两个程序中的其中一个：‘**************************************************

Sub test1()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets

ws.Activate

If ActiveWorkbook.Worksheets.Count > 1 Then

If IsEmpty(https://www.sodocs.net/doc/3218585338.html,edRange) Then

ws.Delete

End If

End If

Next ws

Application.DisplayAlerts = True

End Sub

‘**************************************************

Sub test2()

Dim ws As Worksheet

On Error GoTo Handdle

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets

ws.Activate

With ws

If Application.CountA(.Cells) = 0 Then

.Delete

End If

End With

Next ws

Handdle:

Application.DisplayAlerts = True

End Sub

(二)如果您想删除已打开的工作簿中的所有空工作表，可使用下面的程序：

‘**************************************************

Sub test()

Dim ws As Worksheet

Dim wb As Workbook

Application.DisplayAlerts = False

For Each wb In Workbooks

wb.Activate

For Each ws In ActiveWorkbook.Worksheets

ws.Activate

If ActiveWorkbook.Worksheets.Count > 1 Then

If IsEmpty(https://www.sodocs.net/doc/3218585338.html,edRange) Then

ws.Delete

End If

End If

Next ws

Next wb

Application.DisplayAlerts = True

End Sub