如果范围中的单元格和它旁边的单元格都等于常量，我如何在 VBA 中编写一个循环来删除一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18829055/
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
How do I write a loop in VBA that will delete a row if a cell in a range and the cell next to it both equal a constant?
提问by user2784067
[This is in Excel 2007]
[这是在 Excel 2007 中]
In other words, the loop will cycle through all the active cells in a one-column range (rngAddressName) and, if the cell in the range AND the cell directly to the left of it contain the string "#N/A", then it will delete that row.
换句话说，循环将循环遍历一列范围 (rngAddressName) 中的所有活动单元格，并且如果该范围内的单元格及其直接左侧的单元格包含字符串“#N/A”，则它将删除该行。
Unfortunately, nothing I have tried has had any actual effect. Here is my best go at it:
不幸的是，我尝试过的任何事情都没有产生任何实际效果。这是我最好的做法：
i = 1
For counter = 1 To rngSC2A.Rows.Count
Contents = rngSC2A.Cells(i).Value
If Contents = "#N/A" Then
If rngAddressName.Cells(i).CellOffset(0, -1).Value = "#N/A" Then
rngAddressName.Cells(i).EntireRow.Delete
Else
End If
Else
i = i + 1
End If
Next
But this doesn't seem to find any rows with the conditions satisfied (even though such rows exist in the worksheet). I think it might have something to do with the fact that I am looking in the Cell.Value, but I am not sure.
但这似乎没有找到满足条件的任何行（即使工作表中存在此类行）。我认为这可能与我正在查看 Cell.Value 的事实有关，但我不确定。
回答by Jon Crowell
You can autofilter your range, delete any rows that meet your criteria, then turn the autofilter off. This is a much more efficient approach than looping.
您可以自动过滤您的范围，删除符合您条件的任何行，然后关闭自动过滤器。这是比循环更有效的方法。
The example below works on columns A and B in Sheet1. Modify the variables to reference the range and sheet in your workbook.
下面的示例适用于 Sheet1 中的 A 列和 B 列。修改变量以引用工作簿中的区域和工作表。
Sub DeleteDoubleNA()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("A1:B" & lastRow)
' filter and delete all but header row
With rng
.AutoFilter field:=1, Criteria1:="#N/A"
.AutoFilter field:=2, Criteria1:="#N/A"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
' turn off the filters
ws.AutoFilterMode = False
End Sub
回答by ChrisB
This is a different take on on the excellent answer posted by @Jon Crowell.
这是对@Jon Crowell 发布的优秀答案的不同看法。
If you use an Excel table, you can use the table's ListObjectto get the data range which automatically excludes the header and footer rows.
如果您使用 Excel 表格，您可以使用表格ListObject来获取自动排除页眉和页脚行的数据范围。
This avoids the sometimes incorrect calculation search for a last row.
这避免了对最后一行的有时不正确的计算搜索。
You also want to clear any pre-existing filters on the data so that you don't overlook any rows.
您还希望清除数据上任何预先存在的过滤器，以免忽略任何行。
Dim myTable As Object
Set myTable = ActiveSheet.ListObjects(1) ' Works if worksheet contains only one table
' Clear pre-existing filters on the table
myTable.AutoFilter.ShowAllData
' Filter the table
With myTable.DataBodyRange
.AutoFilter field:=1, Criteria1:="#N/A"
.AutoFilter field:=2, Criteria1:="#N/A"
End With
' Delete visible cells in the filtered table
myTable.DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
' Clear filters on the table
myTable.AutoFilter.ShowAllData
The (1)in ListObjects(1)is the first (in my case only) table in the worksheet. DataBodyRangerefers to the data range of that table excluding header and footer rows.
该(1)中ListObjects(1)是第一个（在我的情况下，只有）在工作表中。 DataBodyRange指的是该表的数据范围，不包括页眉和页脚行。
