CODEWITHSUNDEEP
excelvbarowdelete-row
Sherbeana
Sherbeana

Reputation: 11

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

Upvotes: 0

Views: 2437

Answers (4)

Sherbeana
Sherbeana

Reputation: 11

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!

Upvotes: 1

healey
healey

Reputation: 314

I would tend to do it this way:

Sub DeleteRows()
    Dim i As Integer
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("sample")
    i=1

    While sht.(i,1) <> "" 'Assuming first column is full of data to the bottom
        If sht.Range("AN" & i) = "CAT" Then
              sht.Rows(i).EntireRow.Delete
        Else
            i=i+1
        End If
    Wend
End Sub

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

Below is a sample how to delete rows based on a criteria in column A. Keep in mind that if we delete rows we go backwards to avoid index errors.

Try:

Option Explicit

Sub test()

    Dim Lastrow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        'Where you delete you go backwards
        For i = Lastrow To 2 Step -1
            If .Range("A" & i).Value = "CAT" Then
                .Rows(i).EntireRow.Delete
            End If

        Next i

    End With

End Sub

Upvotes: 1

Tolure
Tolure

Reputation: 879

A site that might be able to help you be the following.

https://www.excelcampus.com/vba/delete-rows-cell-values/ I adjusted the code a little. 

Sub Delete_Rows_Based_On_Value()
'Apply a filter to a Range and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values

    Dim ws As Worksheet

      'Set reference to the sheet in the workbook.
      Set ws = ThisWorkbook.Worksheets("sampel")
      ws.Activate 'not required but allows user to view sheet if warning message appears

      'Clear any existing filters
      On Error Resume Next
        ws.ShowAllData
      On Error GoTo 0

      '1. Apply Filter
      ws.Range("AN3:BG1000").AutoFilter Field:=1, Criteria1:="<>CAT"

      '2. Delete Rows
      Application.DisplayAlerts = False
        ws.Range("B1:G1000").SpecialCells(xlCellTypeVisible).Delete
      Application.DisplayAlerts = True

      '3. Clear Filter
      On Error Resume Next
        ws.ShowAllData
      On Error GoTo 0

    End Sub

Upvotes: 0

Related Questions