VBA to delete entire row based on certain filters

[AEL8871]

I have a bunch of filters I'd like to automate on a spreadsheet. All based on a Yes/No/Blank cell value

For example:
If Column N contains "No" in any row, delete the entire row.
If Column J contains "Yes" in any row, delete the entire row.
If Column K contains blanks in any row, delete the entire row.
If column M contains "No" in any row, delete the entire row.
In column L contains "Yes" in any row, delete the entire row

 

[vcoolio]

Hello AEL8871,

This may work for you but based on the following assumptions (as you haven't explained the set out of your worksheet):-

- Data is in Sheet1(this is the sheet code not sheet name).
- Headings are in row1 with data starting in row2.
- The column range is A:P.
- Column Q can be used as a helper column to determine that the criteria exists in each row for Columns J, K, L, M, N. This is done with a temporarily inserted formula.

Sub Test()

        Dim lr As Long: lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

        Sheet1.Range("Q2:Q" & lr).Formula = "=SUM(COUNTIF(A2:P2,{""Yes"",""No"",""""}))"

        With Sheet1.[A1].CurrentRegion
                .AutoFilter 17, ">0"
                .Offset(1).EntireRow.Delete
                .AutoFilter
        End With

        Sheet1.Columns(17).Clear

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[mumps]

Try this revision of Fluff's code:

Sub Delrws()
    Dim ary As Variant, i As Long
    ary = Array("No", "Yes")
    With Range("J:N")
        For i = 0 To UBound(ary)
           .Replace ary(i), ""
        Next i
        For i = 10 To 14
          Columns(i).SpecialCells(xlBlanks).EntireRow.Delete
        Next i
    End With
End Sub