VBA Delete Row
Results 1 to 3 of 3

Thread: VBA Delete Row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Delete Row

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,242
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Delete Row

    Assume your error occurs with line rDel.EntireRow.Delete? If so, would this work?
    Code:
    If Not rDel Is Nothing Then
      rDel.EntireRow.Delete
    End If


  3. #3
    New Member
    Join Date
    Apr 2015
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Delete Row

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •