vb.net 如何根据单元格值在Excel中删除一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18730276/
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 to remove a row in Excel based on cell value?
提问by user2636163
I have an Excel and I want delete some rows based on the particular cell value, but the Excel is very very huge it has got around 75000 rows. I tried writing a normal function to loop through the sheet, check for the cell value and delete the rows if matched, but it is taking very long time. I waited for 30 min and program was still running can some one suggest me how to achieve this.
我有一个 Excel，我想根据特定的单元格值删除一些行，但 Excel 非常非常大，大约有 75000 行。我尝试编写一个普通函数来循环遍历工作表，检查单元格值并删除匹配的行，但这需要很长时间。我等了 30 分钟，程序仍在运行，有人可以建议我如何实现这一点。
Following is the code.
以下是代码。
Private Sub CommandButton1_Click()
Dim i As Integer
For i = Range("D1").End(xlDown).Row To 1 Step -1
If Cells(i, 4) = 7 Then
Rows(i).Delete Shift:=xlUp
End If
Next i
End Sub
I tested this code for small Excel file with 50 rows and it is working fine. But when I try on the excel I wanted to try it kept me waiting for 30 min and could not complete.
我针对 50 行的小型 Excel 文件测试了此代码，并且运行良好。但是当我尝试使用 excel 时，我想尝试它让我等待 30 分钟并且无法完成。
回答by Gary's Student
If you want to delete rows in which column D has the value 7 and there are no blanks in column D, then run:
如果要删除 D 列值为 7 且 D 列中没有空格的行，请运行：
Sub Tachyon()
Dim rD As Range
Set rD = Intersect(Range("D:D"), ActiveSheet.UsedRange)
rD.Replace "7", ""
rD.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
EDIT1:
编辑1：
DO NOT USE THIS MACRO
不要使用这个宏
Testing indicates that not only does it remove rows containing 7 in column D, it also removes rows whose cells containing 77 or 777. It also corrupts values like 17 or 71. We need something better.
测试表明，它不仅会删除 D 列中包含 7 的行，还会删除其单元格包含 77 或 777 的行。它还会破坏像 17 或 71 这样的值。我们需要更好的东西。
EDIT2
编辑2
This version has the problem fixed:
此版本已修复问题：
Sub Tachyon2()
Dim rD As Range
Set rD = Intersect(Range("D:D"), ActiveSheet.UsedRange)
rD.Replace What:="7", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
rD.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
