时间戳

首页 > 解决方案 > 如果在 A、B 和 C 列中同时发现 #N/A，则 VBA 代码删除整行

excel - 如果在 A、B 和 C 列中同时发现 #N/A，则 VBA 代码删除整行

问题描述

我正在尝试编写一个代码，该代码将查看 A、B 和 C 列中行值的特定工作表，并且该行中的值是否同时在 3 列 A、B 和 C 中等于“#N/A”那么整行都会被删除，但是如果A、B、C列的行中没有错误，则不会被清除。我写了下面的代码，但它不起作用。我将衷心感谢您的帮助。先感谢您！。

Sub CleanNA()
Dim ws As Variant
Dim x As Long

ws = Array("Sheet 1", "Sheet 2", "Sheet 3")
    For x = 0 To UBound(ws)
        ActiveWorkbook.Sheets(ws(x)).Activate
        If IsError(Range("A:C").Value) Then
            ActiveWorkbook.Sheets(ws(x)).UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Clear
            ActiveWorkbook.Sheets(ws(x)).UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Clear
        End If
        Next
Application.ScreenUpdating = True
MsgBox ("#N/A were successfully cleaned")

End Sub

标签： excelvba

解决方案

尝试这个：

Sub CleanNA()
    
    Dim allSheets As Variant
    Dim x As Long, ws As Worksheet, i As Long
    
    Application.ScreenUpdating = False
    
    allSheets = Array("Sheet 1", "Sheet 2", "Sheet 3")
    For x = 0 To UBound(allSheets)
        Set ws = ActiveWorkbook.Sheets(allSheets(x))
        For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
            With ws.Cells(i, 1)
                'count #N/A in A:C for this row
                If Application.CountIf(.Resize(1, 3), CVErr(xlErrNA)) = 3 Then
                    .EntireRow.ClearContents 'clear
                    '.EntireRow.Delete       '...or delete
                End If
            End With
        Next i
    Next x
    
    Application.ScreenUpdating = True
    MsgBox ("#N/A were successfully cleaned")

End Sub

推荐阅读