在列中删除带有错误的所有行 [英] Delete All Rows With Errors in a column

问题描述

我正在处理VBA代码，部分代码需要删除工作表中J列中的文本为＃N / A的所有行。我已经编写了一个代码，但调试时却发现类型不匹配错误。

I am working on a VBA code and part of the code is needed to delete all the rows in the sheet where the text in the column "J" is "#N/A". I have written a code but but I get a Type mismatch error when debugging it.

这里是代码

Dim i As Long
For i = Cells(Rows.Count, 10).End(xlUp).Row To 1 Step -1
If Cells(i, 10) = "#N/A" Then Cells(i, 1).EntireRow.Delete
Next i

推荐答案

使用 Range.SpecialCells方法，将 xlSpecialCellsValue 常量设置为 xlErrors ，以快速识别列J中所有带有错误的单元格。没有公开这些单元格是公式还是键入常量，所以我已经添加了代码，通过 xlCellType枚举类型。

Use the Range.SpecialCells method with the xlSpecialCellsValue constant as xlErrors to quickly identify all cells in column J with errors. It wasn't disclosed whether the cells were formulas or typed constants so I've added code to examine both through the xlCellType Enumeration types.

Sub del_error_rows()
    With Worksheets("Sheet3")
        On Error Resume Next
        With .Columns(10).SpecialCells(xlCellTypeFormulas, xlErrors)
            .EntireRow.Delete
        End With
        With .Columns(10).SpecialCells(xlCellTypeConstants, xlErrors)
            .EntireRow.Delete
        End With
        On Error GoTo 0
    End With
End Sub

On Error Resume Next 是必需的，以防没有单元格那个特定的错误配置。在这种情况下，SpecialCells将是 Nothing ，您必须绕过任何尝试无法处理的错误。

The On Error Resume Next is necessary in case there are no cells with that particular error configuration. In that case, the SpecialCells would be Nothing and you must bypass any error thrown from attempting to handle nothing.

这篇关于在列中删除带有错误的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！