VBA Excel代码，如果发现N/A，则删除行 [英] VBA excel code to delete the rows if N/A is found

问题描述

我正在寻找一种在整个列中查找 N/A 单元格，然后在找到 N/A 的情况下删除整行的方法.我找到了此VBA代码，但对我来说不起作用，因为它仅选择一列.请帮忙(我正在Excel Mac 2011中处理约300000行)

I am looking for a way to find the N/A cells in the entire columns and then delete the entire row if N/A is found. I found this VBA code, but it just doesn't work for me because it selects only one column. Please help (I am working on around 300000 rows in Excel Mac 2011)

Sub DeleteBlankARows() 
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        Dim r As Long 
        For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1 
            If Cells(r, 11) = "" Then Rows(r).Delete 
        Next r 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
    End With 
End Sub 

推荐答案

尝试一下(在Windows XP/Excel 2007上进行了测试，但在Mac/Office 2011上也可以使用):

Try this (tested on Windows XP / Excel 2007 but it should work on Mac / Office 2011):

Option Explicit

Sub DeleteNARows()
    Dim r As Long
    Dim iCol As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        For iCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            For r = Cells(Rows.Count, iCol).End(xlUp).Row To 1 Step -1
                If Application.WorksheetFunction.IsNA(Cells(r, iCol)) Then Rows(r).Delete
            Next r
        Next iCol
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

请注意，您可以(可能不得不)在代码开头更改要检查#N/A 的列

Note that you can (and probably have to) change the column where you want to check fot #N/A at the begining of the code

这篇关于VBA Excel代码，如果发现N/A，则删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！