如果发现 N/A 则删除行的 VBA excel 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10612072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA excel code to delete the rows if N/A is found
提问by soso101
I am looking for a way to find the N/Acells in the entire columns and then delete the entire row if N/Ais 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)
我正在寻找一种方法来查找N/A整列中的单元格，然后如果N/A找到则删除整行。我找到了这个 VBA 代码，但它对我不起作用，因为它只选择了一列。请帮忙（我正在 Excel Mac 2011 中处理大约 300000 行）
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
采纳答案by JMax
Try this (tested on Windows XP / Excel 2007 but it should work on Mac / Office 2011):
试试这个（在 Windows XP / Excel 2007 上测试过，但它应该可以在 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
Note that you can (and probably have to) change the column where you want to check fot #N/Aat the begining of the code
请注意，您可以（并且可能必须）更改要#N/A在代码开头检查的列
回答by brettdj
Another way would be to use Findto quickly test what cells were NA()(I have assumed your are testing for =NA()as formula cells - I can update if they are, or also can be text)
另一种方法是使用Find快速测试单元格是什么NA()（我假设您正在测试=NA()作为公式单元格 - 如果它们是，我可以更新，或者也可以是文本）
This code uses SpecialCellsto search only error values.
此代码用于SpecialCells仅搜索错误值。
Sub GetNA()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim strFA As String
On Error Resume Next
Set rng1 = ActiveSheet.UsedRange.SpecialCells(xlFormulas, xlErrors)
On Error GoTo 0
If rng1 Is Nothing Then
MsgBox "No NA() cells"
Exit Sub
End If
With rng1
Set rng2 = .Find("=NA()", LookIn:=xlFormulas)
If Not rng2 Is Nothing Then
strFA = rng2.Address
Set rng3 = rng2.EntireRow
Do
Set rng2 = .FindNext(rng2)
Set rng3 = Union(rng3, rng2.EntireRow)
Loop While Not rng2 Is Nothing And rng2.Address <> strFA
End If
If Not rng3 Is Nothing Then rng3.Delete
End With
End Sub
