VBA Delete Row

[hanz91]

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!

 

[JackDanIce]

Assume your error occurs with line rDel.EntireRow.Delete? If so, would this work?

If Not rDel Is Nothing Then
  rDel.EntireRow.Delete
End If

 

[jgann]

Ok I have added the code and at least I am not getting an error, but my loop does not go past The first Next>

For Each r In U
v = r.Value
For i = 0 To 15
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
If Not rDel Is Nothing Then
rDel.EntireRow.Delete
End If
End Sub