asked on

vba error if nothing found in list

The code works well, but if there is nothing to remove, I get an error.
Emergency here...can anyone help?

Sub Remove_Intl()
Dim CalcMode As LongPtr

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Sheets("Data")

        With Intersect(.UsedRange, .Range("AY:AY"))
            .Replace What:="International", Replacement:="#N/A", LookAt:= _
                     xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
            .SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
        End With

    End With

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub
Sub Remo

Open in new window

Shums Faruk

Try below:

Sub Remove_Intl()
Dim CalcMode As LongPtr

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    
    On Error Resume Next

    With Sheets("Data")

        With Intersect(.UsedRange, .Range("AY:AY"))
            .Replace What:="International", Replacement:="#N/A", LookAt:= _
                     xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                     ReplaceFormat:=False
            .SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete
        End With

    End With
    
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With

End Sub

Open in new window

SOLUTION