Error with no advanced filter criteria being met

[Ark68]

Below is a snippet of code (graciously assisted by Colin_L and VoG) I use to delete rows of a database isolated by an advanced filter.

It works greats when there are rows that meet the criteria. Although it hasn't happened yet, it is inevitable that at some point the filter will result in no rows needing to be deleted. I will receive an error no doubt when it tries to delete visual cells that are not there.

What error protection method can I build into this code to prevent a break from occuring? I have 5 instances of similar code part of the entire routine each filtering and deleting different sets of criteria, so uncertain whether or not protection can be global or for each separate filter attempt.

' *** Delete CLASS C facilities ***
'
    With Worksheets("Fields_Regular")
        Dim iFCCdel As Integer
            With .Range("A1:K6000")
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("G16:G17"), _
                    Unique:=False
                    iFCCdel = WorksheetFunction.CountIf(Columns("A"), "C")
                    MsgBox "CLASS C RECORDS: " & iFCCdel
            
            On Error Resume Next
            Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        End With

        If .FilterMode Then .ShowAllData
    End With

    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
'
Exit Sub

ErrorHandler:
    MsgBox Err.Number & vbLf & Err.Description
    Resume ErrorExit

Jenn

 

[Colin Legg]

Hi Jenn,

Good thinking, it's always best to be prepared for different scenarios.

I think we've done an OK job here though, it looks to me like we've already put in defensive coding to cater for this possibility. Which line do you think will error?

 

[Ark68]

If I use the filter criteria Fruit (G17) = "Apples"(H17), if the filter finds no instance of fruit in the database, it will turn up with no rows. The filtered database will be empty with the exception of the header row.

The code will then try to delete all the rows, minus the offset header row, all the visible rows found in the filtered dataset. So will the code not stop with an error at either

Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

OR

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

?

Ideally, if the code runs into this scenario, it should continue onto the next section of code, and perhaps leave a message to the user that the filter did not encounter any data for that particular criteria.

As I had mentioned, I use similar code to filter 5 different groups, so the message would have to be variable.

I assume that

ErrorHandler:
    MsgBox Err.Number & vbLf & Err.Description
    Resume ErrorExit

is the code in place for when the filter encounters a null find?

Since I use similar code in 5 different case, I only have one instance of this snippit. Again, I assume that all 5 filter attempts will rely on this error code if encountering a null find at any one of them?

If that is the case, will assigning a messagebox as a variable within each individual filter code and then displaying it in the ErrorHandler code provide that variable message? The ErrorHandler code has the sub ending at that point. Rather than ending though, action ust continue to the next filter code. Only if it reaches an error on the last filter should it exit.

Confused?

Jenn

Jenn

 

[Colin Legg]

Hi Jenn,

No it won't error in that scenario - we have already defensively coded to allow for no visible cells.

If there are no visible cells (excluding the header row) then nothing will be assigned to the rngToDelete range variable here:

Set rngToDelete = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)

Then, when we get to the point where a range may be deleted:

If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete

we check that rngToDelete has a reference to a range. If rngToDelete is nothing then it will not try to delete anything.

Give it a test on a backup of your workbook. Give it some criteria which won't be met and see if it errors out.