VBA if instr statement

Question

Hello everyone,&nbsp;This is my first post here and I am learning VBA macro coding since yesterday.&nbsp;I would like to automatize a part of my work process, by making a macro.&nbsp;Every day I receive a workbook including around 100 worksheets, and I want to delete worksheets NOT CONTAINING a text in a specific range (for example, keep every sheet having "IVD" within range A20:AE80 and delete all other sheets).&nbsp;I tried using the If Not InStr statement, but it does not work... (Run time error 13 : type mismatch)&nbsp;Here below the code I have wrote, I would greatly appreciate every help.&nbsp;&nbsp;Sub auto_delete()

Dim ws As Worksheet

Dim IVDrange As range

Set IVDrange = range("A20:AE80")

If Not InStr(IVDrange, "IVD") Then ws.Delete

End Sub&nbsp;&nbsp;

hansvogelaar · Accepted Answer

Soran0311&nbsp;
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:

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
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

Forum Discussion

VBA if instr statement

2 Replies

Resources