excel - 循环遍历工作表以删除某些单元格

Question

我希望从工作簿的工作表中删除特定单元格。这样做的同时，它还应该删除工作簿的这些工作表中存在公式错误的特定单元格。我在 stackoverflow 中使用了 @Blind Seer 的最新程序，按照以下链接，该程序适用于类似的应用程序。

incorporating-sheet-loop

下面附有程序运行前的工作簿样本

我采用的代码如下。

Sub DeleteCells()

Dim rng As Range, rngError As Range, delRange As Range
Dim i As Long, j As Long, k As Long
Dim wks As Worksheet

On Error Resume Next

Set rng = Application.InputBox("Select cells To be deleted", Type:=8)

On Error GoTo 0

If rng Is Nothing Then Exit Sub Else rng.Delete

For k = 1 To ThisWorkbook.Worksheets.Count 'runs through all worksheets

  Set wks = ThisWorkbook.Worksheets(k)

  With wks

    For i = 1 To 7 '<~~ Loop trough columns A to G

        '~~> Check if that column has any errors
        On Error Resume Next

        Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors)

        On Error GoTo 0

        If Not rngError Is Nothing Then
            For j = 1 To 100 '<~~ Loop Through rows 1 to 100
                If .Cells(j, i).Text = "#DIV/0!" Then
                    '~~> Store The range to be deleted
                    If delRange Is Nothing Then
                        Set delRange = .Columns(i)
                        Exit For
                    Else
                        Set delRange = Union(delRange, .Columns(i))
                    End If
                End If
             Next j
         End If

     Next i

  End With

Next k

'~~> Delete the range in one go
If Not delRange Is Nothing Then delRange.Delete

End Sub

运行代码后，它会删除输入框中输入的单元格，即清空单元格中的数据，并将其余数据附加到最后填充行末尾的行中。它没有清除错误单元格，程序给出错误消息: 方法“object_Global 的联合失败”

在以下代码行

'设置 delRange = Union(delRange, .Columns(i))'

下面附加了程序运行后的示例数据。

请帮忙找出程序中的错误。所需的结果是输入单元格范围应空白，保留其行位置。对于错误单元格也是如此。

谢谢

Answer 1

Option Explicit

Sub DeleteCells()
    Dim ws As Worksheet, rng As Range, rngErr As Range

    On Error Resume Next

    Set rng = Application.InputBox("Select cells to be deleted", Type:=8)

    If Not rng Is Nothing Then
        rng.Delete
        For Each ws In ThisWorkbook.Worksheets
            Set rngErr = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
            If Not rngErr Is Nothing Then rngErr.Clear
        Next
    End If
End Sub