Excel VBA 删除单元格包含搜索字符串的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22485335/
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
Excel VBA delete row where cell contains search string
提问by Ephs05msm
Trying to loop through a column, searching for "Total" within cells that also contain other text (e.g., "January Total", "Boston Total", "Office Total", "Company ABC Total"), and deleting entire rows where "Total" is found. Here's what I have (r and rcell are defined as range objects):
尝试遍历一列，在还包含其他文本的单元格中搜索“总计”（例如，“一月总计”、“波士顿总计”、“办公室总计”、“公司 ABC 总计”），并删除“总”找到。这是我所拥有的（r 和 rcell 被定义为范围对象）：
Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
For Each rcell In r
If Not Excel.WorksheetFunction.Search("Total", rcell) = "#VALUE!" Then
Rows(target.Row).Delete
Else
'do nothing
End If
Next
I'm trying to pursue the logic that says, "If the cell doesn't contain 'Total', it will return an error. So, for all the cells that don't return an error, delete the row." My syntax is clearly wrong. I've visited a number of similar questions here on the site and haven't been able to substitute working code.
我试图遵循这样的逻辑，“如果单元格不包含 'Total'，它将返回一个错误。因此，对于所有不返回错误的单元格，删除该行。” 我的语法显然是错误的。我在网站上访问了许多类似的问题，但无法替换工作代码。
In standard Excel formulas, this would be an IF(ISERROR(SEARCH(...))...).
在标准 Excel 公式中，这将是 IF(ISERROR(SEARCH(...))...)。
Thank you very much for the help.
非常感谢你的帮助。
回答by Doug Glancy
Just in case you come back this way, I thought I'd demonstrate what I meant in my comment. It's true that the Macro Recorder creates a literal array of all the values that contain "Total." Your generated code looked something like this, I'll bet:
以防万一你以这种方式回来，我想我会在我的评论中证明我的意思。确实，宏记录器创建了一个包含所有包含“Total”的值的文字数组。你生成的代码看起来像这样，我敢打赌：
Sub Macro1()
ActiveSheet.Range("$B:$B").AutoFilter Field:=1, _
Criteria1:=Array("Boston Total", "Chicago Total", "December Total", "February Total", _
"January Total", "January Total Total", "November Total", "October Total", _
"October Total Total", "San Francisco Total"), Operator:=xlFilterValues
Rows("16:29").Select
Selection.ClearContents
End Sub
The trick is to replace that array of values with a single value that uses wildcards, i.e., "*Total*". Combining that with your LastRowand other variables, I come up with:
诀窍是用使用通配符的单个值替换该值数组，即"*Total*". 将它与您LastRow和其他变量相结合，我想出了：
Sub DeleteTotals()
Dim LastRow As Long
Dim r As Excel.Range
With ActiveSheet
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
Set r = .Range(Cells(4, "B"), Cells(LastRow, "B"))
End With
With r
.AutoFilter Field:=1, Criteria1:="*Total*", Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
This kind of code is faster than looping. It may not matter if you don't have much data, but it's good to know you can do it this way.
这种代码比循环更快。如果您没有太多数据可能并不重要，但很高兴知道您可以通过这种方式做到这一点。
回答by Gary's Student
Consider:
考虑：
Sub marine()
Dim r As Range, rDelete As Range
Set rDelete = Nothing
LastRow = 100
Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
For Each rcell In r
v = CStr(rcell.Value)
If InStr(1, v, "Total") > 0 Then
If rDelete Is Nothing Then
Set rDelete = r
Else
Set rDelete = Union(r, rDelete)
End If
End If
Next rcell
If rDelete Is Nothing Then
Else
rDelete.EntireRow.Delete
End If
End Sub
Use your own code to calculate LastRow
使用您自己的代码计算LastRow
EDIT#1:
编辑#1：
Here is the corrected version:
这是更正后的版本：
Sub marine()
Dim r As Range, rDelete As Range
Set rDelete = Nothing
LastRow = 100
Set r = Range(Cells(4, "B"), Cells(LastRow, "B"))
For Each rcell In r
v = CStr(rcell.Value)
If InStr(1, v, "Total") > 0 Then
If rDelete Is Nothing Then
Set rDelete = rcell
Else
Set rDelete = Union(rcell, rDelete)
End If
End If
Next rcell
If rDelete Is Nothing Then
Else
rDelete.EntireRow.Delete
End If
End Sub
