Error Handling - Advanced Filter

[Ark68]

Looking for assistance in error handling when an advanced filter turns up negative results for deletion.

Here is a snippit of code ...

With Worksheets("Data")
        If .FilterMode Then .ShowAllData
        llastrow = Range("a65536").End(xlUp).Row
        With .Range("A1:M" & llastrow)
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("A1:D2"), _
                    Unique:=False
            Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            lredrow = rngToDelete.Count
            ' MsgBox ("Redundant Rows Found: ") & lredrow
            Sheets("Main").Activate
            Range("D7").Value = lredrow
                 
        End With

"No cells were found." Error at red highlighted line.

Jenn

 

[schielrn]

You can do something like:

With Worksheets("Data")
        If .FilterMode Then .ShowAllData
        llastrow = Range("a65536").End(xlUp).Row
        With .Range("A1:M" & llastrow)
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("A1:D2"), _
                    Unique:=False
            On Error GoTo Handler
            Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            lredrow = rngToDelete.Count
            ' MsgBox ("Redundant Rows Found: ") & lredrow
            Sheets("Main").Activate
            Range("D7").Value = lredrow
                 
        End With
Exit sub
Handler:
msgbox "No cells were found!"
End Sub

Hope that helps.

 

[Ark68]

Thank you schielrn, this is doing the trick, however, I have two considerations that need to be dealt with.

1) When it reaches the error, it's great to advise the user with the MsgBox, but how do I resume the code after the error? There is more to the sub after the filter application.
2) I have several advanced filters applied at different points in this sub. Does this improvement handle similar (no cells found) errors in the additional filters? If so, how would I go about differentiating the MsgBox to indicate the specific filter results (ie. no x rows found; no y rows found etc ... where x and y etc. are different filters)?

Jenn

 

[schielrn]

You could possibly put in an On Error Resume Next statement, but that will skip over all errors and may do some things you do not want it to do?

With Worksheets("Data")
        If .FilterMode Then .ShowAllData
        llastrow = Range("a65536").End(xlUp).Row
        With .Range("A1:M" & llastrow)
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("A1:D2"), _
                    Unique:=False
            On Error Resume Next
            Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            lredrow = rngToDelete.Count
            ' MsgBox ("Redundant Rows Found: ") & lredrow
            Sheets("Main").Activate
            Range("D7").Value = lredrow
                 
        End With

Try that on a copy and see if it is doing what you want, otherwise we can maybe explore other options.

 

[Ark68]

Hi schielrn,

I liked the Handler: method as it allowed me to provide some user notification as well as some other code (which is why it's important to know whether this same code will handle all the advanced filter errors in the same way, which will not provide the results I need).

I have added Resume Next as the last line of my Handler: code, but what is happening when I run the code now, and it finds an error, it executes the Handler: code twice. It goes through it once, returns to the next line, and does it again, and then resumes the rest of the code as it should. No need for the second error handling.

Jenn

 

[schielrn]

Can you maybe post your whole code?