Search code examples
excelvbarowdelete-row

Delete row if cells equal a set of values


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

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


Solution

  • 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
    

    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
    

    Thanks!