vba 如何根据单元格值删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14613609/
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 delete row based on cell value
提问by sikas
I have a worksheet, I need to delete rows based on cell value ..
我有一个工作表，我需要根据单元格值删除行..
Cells to check are in Column A ..
要检查的单元格位于 A 列 ..
If cell contains "-" .. Delete Row
如果单元格包含 "-" .. 删除行
I can't find a way to do this .. I open a workbook, copy all contents to another workbook, then delete entire rows and columns, but there are specific rows that has to be removed based on cell value.
我找不到这样做的方法。我打开一个工作簿，将所有内容复制到另一个工作簿，然后删除整个行和列，但必须根据单元格值删除特定行。
Need Help Here.
在这里需要帮助。
UPDATE
更新
Sample of Data I have
我拥有的数据样本
采纳答案by Peter L.
The screenshot was very helpful - the following code will do the job (assuming data is located in column A starting A1):
屏幕截图非常有用 - 以下代码将完成这项工作（假设数据位于从 A1 开始的 A 列中）：
Sub RemoveRows()
Dim i As Long
i = 1
Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count
If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 1).Text, "-", vbTextCompare) > 0 Then
ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
Else
i = i + 1
End If
Loop
End Sub
Sample file is shared: https://www.dropbox.com/s/2vhq6vw7ov7ssya/RemoweDashRows.xlsm
示例文件共享：https: //www.dropbox.com/s/2vhq6vw7ov7ssya/RemoweDashRows.xlsm
回答by John Bustos
The easiest way to do this would be to use a filter.
最简单的方法是使用过滤器。
You can either filter for any cells in column A that don't have a "-" and copy / paste, or (my more preferred method) filter for all cells that do have a "-" and then select all and delete - Once you remove the filter, you're left with what you need.
您可以过滤 A 列中没有“-”的任何单元格并复制/粘贴，或者（我更喜欢的方法）过滤所有具有“-”的单元格，然后全选并删除 - 一次你移除过滤器，你只剩下你需要的东西。
Hope this helps.
希望这可以帮助。
回答by weir
You could copy down a formula like the following in a new column...
您可以在新列中复制如下公式...
=IF(ISNUMBER(FIND("-",A1)),1,0)
... then sort on that column, highlight all the rows where the value is 1 and delete them.
...然后对该列进行排序，突出显示值为 1 的所有行并删除它们。
回答by zaheer
if you want to delete rows based on some specific cell value. let suppose we have a file containing 10000 rows, and a fields having value of NULL. and based on that null value want to delete all those rows and records.
如果要根据某些特定单元格值删除行。假设我们有一个包含 10000 行的文件，以及一个值为 NULL 的字段。并基于该空值想要删除所有这些行和记录。
here are some simple tip. First open up Find Replace dialog, and on Replace tab, make all those cell containing NULL values with Blank. then press F5 and select the Blank option, now right click on the active sheet, and select delete, then option for Entire row.
这里有一些简单的提示。首先打开“查找替换”对话框，然后在“替换”选项卡上，将所有包含 NULL 值的单元格设置为空白。然后按 F5 并选择空白选项，现在右键单击活动工作表，然后选择删除，然后选择整行。
it will delete all those rows based on cell value of containing word NULL.
它将根据包含单词 NULL 的单元格值删除所有这些行。
回答by Jorjo Peralta
If you're file isn't too big you can always sort by the column that has the -and once they're all together just highlight and delete. Then re-sort back to what you want.
如果您的文件不是太大，您总是可以按具有该列的列进行排序-一旦它们全部放在一起，只需突出显示并删除即可。然后重新排序回你想要的。
回答by BeachBum68
This is the autofilter macro you could base a function off of:
这是您可以基于以下功能的自动过滤器宏：
Selection.AutoFilter
ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:="=*-*", Operator:=xlAnd
Selection.AutoFilter
I use this autofilter function to delete matching rows:
我使用这个自动过滤功能来删除匹配的行：
Public Sub FindDelete(sCol As String, vSearch As Variant)
'Simple find and Delete
Dim lLastRow As Integer
Dim rng As Range
Dim rngDelete As Range
Range(sCol & 1).Select
[2:2].Insert
Range(sCol & 2) = "temp"
With ActiveSheet
.usedrange
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Range(sCol & 2, Cells(lLastRow, sCol))
rng.AutoFilter Field:=1, Criteria1:=vSearch, Operator:=xlAnd
Set rngDelete = rng.SpecialCells(xlCellTypeVisible)
rng.AutoFilter
rngDelete.EntireRow.Delete
.usedrange
End With
End Sub
call it like:
称之为：
call FindDelete "A", "=*-*"
It's saved me a lot of work. Good luck!
它为我节省了很多工作。祝你好运！
回答by Francis Dean
You can loop through each the cells in your range and use the InStrfunction to check if a cell contains a string, in your case; a hyphen.
InStr在您的情况下，您可以遍历范围内的每个单元格并使用该函数检查单元格是否包含字符串；一个连字符。
Sub DeleteRowsWithHyphen()
Dim rng As Range
For Each rng In Range("A2:A10") 'Range of values to loop through
If InStr(1, rng.Value, "-") > 0 Then 'InStr returns an integer of the position, if above 0 - It contains the string
rng.Delete
End If
Next rng
End Sub
