如何使用VBA删除工作簿中的空白纸? [英] How to delete a blank sheet in a workbook using VBA?

问题描述

Sub delete()
    Dim sh As Worksheet, wb As String, c As Range
    wb = InputBox("work book name")
    Set sh = Workbooks(wb).Sheets
        For Each Sheet In sh
            If IsEmpty(sh.UsedRange) Then
            sh.delete
            End If
        Next
End Sub

我无法使用上述代码删除空白表.

I am unable to delete the empty sheets using above code.

推荐答案

下面的代码删除当前打开的工作簿中的所有空白工作表

The below code deletes all empty sheets in the currently opened workbook

改为尝试

Sub delete()
    Application.DisplayAlerts = False
    Dim sh As Worksheet
    For Each sh In Sheets
        If IsEmpty(sh.UsedRange) Then sh.delete
    Next
    Application.DisplayAlerts = True
End Sub

如果要使用名称use指定完整路径

if you want to specify the full path with the name use

Sub delete()
    Dim wb As Workbook, s As String
    s = InputBox("Full workbook path & name")

    Dim fileExists As Boolean
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    fileExists = fso.fileExists(s)

    If fileExist Then
        Set wb = Workbooks.Open(s)
        For Each Sheet In sh
            If IsEmpty(sh.UsedRange) Then
            sh.delete
            End If
        Next
    Else
        MsgBox "File doesn't exist", vbCritical, "Error"
    End If

End Sub

这篇关于如何使用VBA删除工作簿中的空白纸?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！