VBA Autofilter IF the criteria does not existing

[Alexsay]

Hello everyone,

I tried to put an autofilter in my VBA code but dunno how to put de IF statement if the criteria does not exist
If the criteria does not exist, I just want the code to proceed forward.

Some info about my file
My workbook is a table format
I have a column for checking with Yes or Not and I want to autofilter "Not" and delete the cells, and its work, but when I do not have "Not" and only "Yes", the autofilter is happened (its activated but disapear all data) and broke the code

This is my autofilter and detele code

Set Rng = range("Table1")
Rng.AutoFilter Field:=54, Criteria1:="NU"
range("Table1[#Headers]").Select
Rng.SpecialCells(xlCellTypeVisible).Select
Rng.SpecialCells(xlCellTypeVisible).Delete

Also, if somebody can tell me how can I skip (or automatically press "OK") on the delete message box

Any answer it will be more than useful
Thank you

 

[Alexsay]

anybody?

 

[Alex Blakenburg]

Give this a try on a copy of your workbook:

Sub DeleteFilteredRows()
    Dim lo As ListObject
    Dim rngDel As Range
    
    Set lo = Range("Table1").ListObject
    
    With lo
        If .ShowAutoFilter = True Then
            .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter Field:=54, Criteria1:="NU"
        
        If .Range.SpecialCells(xlCellTypeVisible).Address <> .HeaderRowRange.Address Then
            Set rngDel = .DataBodyRange.SpecialCells(xlCellTypeVisible)
            .AutoFilter.ShowAllData
            rngDel.Delete
        End If
    
    End With

End Sub

 

[Alex Blakenburg]

The previous code I gave you attempts to only delete the rows from the table this is useful if you have data to the right of the table.
It will however fail if you have a table below the table you are working on that has more columns than this table.
If that is the case you will need to delete the entire row.
The code below will do that:

Sub DeleteFilteredENTIRERows()

    Dim lo As ListObject
    Dim rngDel As Range
  
    Set lo = Range("Table1").ListObject
  
    With lo
        If .ShowAutoFilter = True Then
            .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter Field:=54, Criteria1:="NU"
      
        If .Range.SpecialCells(xlCellTypeVisible).Address <> .HeaderRowRange.Address Then
            Set rngDel = .DataBodyRange.SpecialCells(xlCellTypeVisible)
            Application.DisplayAlerts = False
            rngDel.Rows.Delete
            Application.DisplayAlerts = True
        End If
        .AutoFilter.ShowAllData
    End With

End Sub

 

[Alexsay]

It works!!!
Thanks a lot

 

[Alex Blakenburg]

You're welcome. Glad I could help. Just curious which one did you end up using ?

 

[Alexsay]

The second code works