VBA Auto Filter If Criteria Exists-CodePudding

I've recorded macros to autofilter and delete rows from a table. But this is not dynamic in the sense that if the filter criteria does not exist in a given table then the macro will break.

I am trying to create a code that will autofilter and delete the rows if the the criteria exists or otherwise do nothing. I am trying to follow this post, but I am missing something. Please help.

My code returns no errors, but also does not do anything. I added the message box to make sure that it was actually running.

Here is my code so far:

Sub autofilter()

Dim lo As ListObject

    Set lo = Worksheets("BPL").ListObjects("Table1")
   
    
With Sheets(1)
    If .AutoFilterMode = True And .FilterMode = True Then
        If lo.Parent.autofilter.Filters(7).Criteria1 = "APGFORK" Then
            '
    lo.Range.autofilter Field:=7, Criteria1:="APGFORK"
    
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    lo.autofilter.ShowAllData
            '
        End If
    End If
End With

MsgBox ("Code Complete")

End Sub

CodePudding user response：

I don't know if it is a bug or a feature, but .AutoFilterMode seems to returns False all the time in Excel 2013 or later. All examples I see that use .AutoFilterMode are earlier than that.
I think the replacement is .ShowAutoFilter on the listobject. In your code, lo.ShowAutoFilter should return True or False depending on whether or not the autofilter is set or not.

But the rest of your code seems problematic too. The test If lo.Parent.autofilter.Filters(7).Criteria1 = "APGFORK" Then throws an error and removes the autofilter.

CodePudding user response：

I Ended up taking a different approach:

Dim LastRowG As Long
LastRowG = Range("G" & Rows.Count).End(xlUp).Row

For i = 2 To LastRowG
If Range("G" & i).Value = "APGFORK" Then

    lo.Range.autofilter Field:=7, Criteria1:="APGFORK"
    
    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    
    lo.autofilter.ShowAllData
    Else
    End If

Next i

This way if "APGFORK" does not exist in a data set, it will move on without an error code.