如何使用VBA删除工作簿中的空白表

Excel实用知识 2023-02-28 发布于广东

展开全文

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

我无法使用上面的代码删除空气表。

最佳答案

下面的代码删除当打开的工作仓中的所有空间表

试试这个吧

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

如果你想用名字使用来指定完成整条路径

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