vba 如果单元格包含某些文本，则删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28779015/
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
Delete rows if cell contains certain text
提问by Siti
I want to delete the entire rows for cell that contain 'Total' and 'Nett' in column C.
我想删除 C 列中包含“Total”和“Nett”的单元格的整行。
I have tried the macro recording using Auto Filter but it only delete rows up to a specified range (which may differ if I use other set of data).
我已经尝试使用自动过滤器进行宏录制，但它只能删除指定范围内的行（如果我使用其他数据集，可能会有所不同）。
Appreciate your help!
感谢你的帮助！
回答by KingOfTheNerds
Here you go. Just copy and paste this sub into your file. To use it, select the SINGLE column that you want to evaluate. This will go through every cell that has been selected, and if it matches the criteria, it will delete the entire row. Let me know if you have any questions. Good luck!
干得好。只需将此子文件复制并粘贴到您的文件中即可。要使用它，请选择要评估的 SINGLE 列。这将遍历已选择的每个单元格，如果它符合条件，它将删除整行。如果您有任何问题，请告诉我。祝你好运！
Sub DeleteRows()
'Enter the text you want to use for your criteria
Const DELETE_CRITERIA = "Test"
Dim myCell As Range
Dim numRows As Long, Counter As Long, r As Long
'Make sure that we've only selected a single column
If Selection.Columns.Count > 1 Then
MsgBox ("This only works for a single row or a single column. Try again.")
Exit Sub
End If
numRows = Selection.Rows.Count - 1
ReDim arrCells(0 To 1, 0 To numRows) As Variant
'Store data in array for ease of knowing what to delete
Counter = 0
For Each myCell In Selection
arrCells(0, Counter) = myCell
arrCells(1, Counter) = myCell.Row
Counter = Counter + 1
Next myCell
'Loop backwards through array and delete row as you go
For r = numRows To 0 Step -1
If InStr(1, UCase(arrCells(0, r)), UCase(DELETE_CRITERIA)) > 0 Then
Rows(arrCells(1, r)).EntireRow.Delete
End If
Next r
End Sub
回答by Kyle
This will loop through cells in Column C and delete the entire row if the cell contains either "Total" or "Nett". Keep in mind that it is case sensitive, so the first letter of "Nett" or "Total" would need to be capitalized for this to find it. There can be other text in the cell however. Also note that the references are not fully qualified (ie Workbook().Worksheet().Range()) because you did not provide a workbook or worksheet name. Let me know if this does not work for you.
如果单元格包含“总计”或“净值”，这将遍历 C 列中的单元格并删除整行。请记住，它区分大小写，因此需要将“Nett”或“Total”的第一个字母大写才能找到它。但是，单元格中可以有其他文本。另请注意，引用不是完全限定的（即 Workbook().Worksheet().Range()），因为您没有提供工作簿或工作表名称。如果这对您不起作用，请告诉我。
Sub Delete()
Dim i as Integer
For i = Range("c" & Rows.Count).End(xlUp).Row To 1 Step -1
If Instr(1, Cells(i, 3), "Total") <> 0 Or Instr(1, Cells(i,3),"Nett") <> 0 Then
Cells(i,3).EntireRow.Delete
End If
Next i
End Sub
