Hi, I am trying to write a code to delete rows based on word in a particular column. I noticed that if the words that should trigger deletion of the row are absent from my file, then I get an error message (Run-time error '91' Object variable or with block variable not set). Is there any way to alter the code so that even when the trigger words are absent, the macro does not crash?
Sub ForeseeuploadCallTypes()
Dim Word(0 To 4) As String
Word(0) = "Uncategorized"
Word(1) = "Merchant Inquiries"
Word(2) = "Autoreply/Spam"
Word(3) = "OB – Confirm Booking"
Word(4) = "OB - Confirm Cancellation"
Dim rDel As Range, U As Range, r As Range
Set rDel = Nothing
Set U = Range("F2:F12000")
For Each r In U
v = r.Value
For i = 0 To 4
If v = Word(i) Then
If rDel Is Nothing Then
Set rDel = r
Else
Set rDel = Union(r, rDel)
End If
End If
Next
Next
rDel.EntireRow.Delete
End Sub
This is a problem because this macro is part of a 'call macro' series and when it crashes it prevents subsequent macros that are part of the series from running.
Really appreciate the help!
Thanks!
Sub ForeseeuploadCallTypes()
Dim Word(0 To 4) As String
Word(0) = "Uncategorized"
Word(1) = "Merchant Inquiries"
Word(2) = "Autoreply/Spam"
Word(3) = "OB – Confirm Booking"
Word(4) = "OB - Confirm Cancellation"
Dim rDel As Range, U As Range, r As Range
Set rDel = Nothing
Set U = Range("F2:F12000")
For Each r In U
v = r.Value
For i = 0 To 4
If v = Word(i) Then
If rDel Is Nothing Then
Set rDel = r
Else
Set rDel = Union(r, rDel)
End If
End If
Next
Next
rDel.EntireRow.Delete
End Sub
This is a problem because this macro is part of a 'call macro' series and when it crashes it prevents subsequent macros that are part of the series from running.
Really appreciate the help!
Thanks!