如果单元格等于一组值，则删除行 [英] Delete row if cells equal a set of values

问题描述

我创建了一个宏，以便生成每日报告。宏中在AN列中找到一个值并删除整个行的部分（编辑为从最后使用的行开始删除行的代码）效果很好。
下面的示例删除在列AN中不包含 CAT， BAT或 DOG值的所有行。

I created a macro to in order to generate a daily report. The portion of the macro that finds a value in column AN and deletes the entire row (code edited to delete rows starting from the last used row), works well. The following example deletes all the rows that do not contain the value "CAT","BAT", or "DOG in column AN.

'False screen updating
  Application.ScreenUpdating = False
'deleting all other types other than CAT from "samples" tab (excluding the header row, row 1)
  Sheets("sample").Select
  Lastrow = Cells(Rows.Count, "AN").End(xlUp).Row
'Deleting rows from bottom up
    For i = Lastrow To 2 Step -1
        If Range("AN" & i).Value <> "CAT" And _
           Range("AN" & i).Value <> "BAT" And _
           Range("AN" & i).Value <> "DOG" Then
             Rows(i).EntireRow.Delete
        End If
    Next i

但是，要创建另一个Sub来删除所有做包含一组特定的值。
我尝试用 = 和 == 替换<> >，但是都无效，也没有行被删除

However, would like to create another Sub that deletes all the rows that do contain a specific set of values. I tried replacing <> with = and ==, however neither worked and no rows were deleted

推荐答案

感谢大家为解决此问题提供的帮助。我发现问题的根本原因仅仅是If / Then行末的条件语句。 And _ 语句的意思是如果单元格等于CAT，BAT和DOG，则删除行，而不是如果单元格等于CAT，BAT或DOG，则删除行。用 Or _ 替换 And _ 已解决此问题。

Thank you everyone for help resolving this issue. I have found that the root cause of my problem was simply the condition statement at the end of my If/Then line. The "And_" statement was saying "If cell equals CAT and BAT and DOG, then delete row" NOT "If cell equals CAT or BAT or DOG, then delete row". Replacing "And_" with "Or_" has fixed this issue.

'False screen updating
  Application.ScreenUpdating = False
'deleting all other types other than CAT from "samples" tab (excluding the header row, row 1)
  Sheets("sample").Select
  Lastrow = Cells(Rows.Count, "AN").End(xlUp).Row
'Deleting rows from bottom up
    For i = Lastrow To 2 Step -1
        If Range("AN" & i).Value = "CAT" Or _
           Range("AN" & i).Value = "BAT" Or _
           Range("AN" & i).Value = "DOG" Or _
           Range("AN" & i).Value = "" Then
             Rows(i).EntireRow.Delete
        End If
    Next i

但是，如果单元格为，我还想删除行空白 。 Sub为什么会忽略此行？

However, I would also like to delete rows if the cells is Blank "". Why would the Sub ignore this line?

  Range("AN" & i).Value = "" Then

谢谢！

这篇关于如果单元格等于一组值，则删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！