Excel VBA If there is a run error, ignore a group of codes associated with it

Question

This is a sample of what I'm putting on my macro:

Cells.Find(What:="M104", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
Selection.EntireRow.Delete

If excel can't find "M104", then it gives you a error message and the macro won't run.

I tried "On Error Resume Next" before the coding above and it ignored the find error, but deleted the row. Is there a way to make it ignore both the find function and the row deletion?

Thanks for the help!

Answer 1

On Error Resume Next does exactly what it says it does: resumes execution of code at the next line.

The error arises because you've piggy-backed the Activate on to a method that might return Nothing.

Here is a very crude way of handling this error:

On Error Resume Next
Cells.Find(What:="M104", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
'If there was not an error, then delete the row
If Err.Number = 0 Then 
    Selection.EntireRow.Delete
End If
'Resume the default error handling
On Error GoTo 0

Here is a more sophisticated way. First, declare a Range object to hold the result of the Find.

Dim foundRange as Range
Set foundRange = Cells.Find(What:="M104", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
If Not foundRange Is Nothing Then foundRange.EntireRow.Delete

In the second example, I have programmed to anticipate the error, and built some logic to handle the potential case of "not found".

You can also use error handling GoTo blocks with Resume _line_ option, but that's generally more cumbersome and I try to avoid that where possible