VBA代码，用于对所有工作表和名称未知的工作簿执行操作

Question

我有很多报告，其中都有我不需要的小计。它们都在一行上，带有唯一编号，然后是"总计"（粗体）。我正在尝试创建将应用于我所有工作簿的代码，因为我创建新的工作簿将查找并删除包含"总计"（粗体）文本的任何行。我知道如何将它应用于具有我知道的名称的特定工作簿/工作表，但不知道如何让它应用于任何名称的工作表。

    Sub Find_Delete_Total()
    Dim Total_Row As Long
    Dim X As Long
    For Each Ws In ActiveWorkbook.Worksheets

     With Ws
            With Application.FindFormat.Font
                .FontStyle = "Bold"
                .Subscript = False
                .TintAndShade = 0
            End With
            For X = 1 To 10000

        Total_Row = Cells.Find(What:="Total", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Select.Row
                    Selection.Delete Shift:=xlUp
            Next X
        End With
    Next Ws
End Sub

Answer 1

您修改后的代码缺少与With ActiveSheet对应的End With。此外，将ActiveSheet更改为 Sht ，并限定Cells.Find中的相应对象。

我做了一些修改，以避免在找不到"总计"的情况下出错。

Sub Find_Delete_Total()
Dim Total_Row As Long
Dim X As Long
Dim delRow as Range
Dim Sht as Worksheet
For Each Sht In ActiveWorkbook.WorkSheets
    With Application.FindFormat.Font
        .FontStyle = "Bold"
        .Subscript = False
        .TintAndShade = 0
    End With
    With Sht
        For X = 1 To 10000
            Set delRow = .Cells.Find(What:="Total", _
                After:=.Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False, _
                SearchFormat:=True)
           If Not delRow Is Nothing Then 
               delRow.EntireRow.Delete Shift:=xlUp
           End If
        Next X
    End With
Next Sht
End Sub

Answer 2

For Each ws In ActiveWorkbook.Worksheets
....
Next ws