May 20 2021 01:07 AM
Hello everyone,
This is my first post here and I am learning VBA macro coding since yesterday.
I would like to automatize a part of my work process, by making a macro.
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).
I tried using the If Not InStr statement, but it does not work... (Run time error 13 : type mismatch)
Here below the code I have wrote, I would greatly appreciate every help.
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
May 20 2021 01:51 AM
SolutionInStr 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:
May 20 2021 03:19 AM
May 20 2021 01:51 AM
SolutionInStr 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: