VBA if instr statement

Soran0311 

InStr works with a single string, not with a series of strings in a multi-cell range.

You can use the Find method of the range instead.

Also, you'll need to loop through the worksheets. Since you are going to delete some of them, you must loop backwards from the last one to the first. Deleting a sheet while looping forwards is like pulling the rug from under yourself.

Sub auto_delete()
    Dim i As Long
    Dim ws As Worksheet
    Dim IVDrange As Range
    Dim FoundRange As Range
    
    ' Speed up execution by not updating the screen
    Application.ScreenUpdating = False
    ' Suppress the warning message when we delete a sheet
    Application.DisplayAlerts = False
    
    ' Loop backwards through the worksheets
    For i = Worksheets.Count To 1 Step -1
        ' Assign worksheet variable
        Set ws = Worksheets(i)
        ' Specify the range
        Set IVDrange = Range("A20:AE80")
        ' See if "IVD" can be found
        Set FoundRange = IVDrange.Find(What:="IVD", LookAt:=xlWhole, MatchCase:=False)
        ' If not found ...
        If FoundRange Is Nothing Then
            ' ... then delete the sheet
            ws.Delete
        End If
    Next i
    
    ' Display warnings again
    Application.DisplayAlerts = True
    ' Update the screen again
    Application.ScreenUpdating = True
End Sub

Remarks:

1. The part LookAt:=xlWhole specifies that the code looks for cells whose entire value is "IVD", without anything else. If you want to search for cells that contain "IVD" plus possibly other text, use LookAt:=xlPart
2. The part MatchCase:=False specifies that the search is not case-sensitive: it will find "ivd" and "IvD" as well as "IVD". If you want to make sure that only "IVD" is found, use MatchCase:=True