Excel VBA delete rows Object Variable or With block variable not set

Question

Sub project()
    Dim a As Long
    Dim rKill As Range
    a = 3
    Do
        If Abs(Cells(a - 1, 7).Value - Cells(a, 7).Value) > 5 And Abs(Cells(a + 1, 7).Value - Cells(a, 7).Value) > 5 Then
            If rKill Is Nothing Then
                Set rKill = Rows(a)
            Else
                Set rKill = Union(rKill, Rows(a))
            End If
        End If
        a = a + 2
    Loop Until Cells(a, 7).Value = ""
    rKill.EntireRow.Delete
End sub

I am trying to delete multiple rows, to do that, I set a range rKill and rKill is a union of all rows I am going to delete. However, I am having following problems:

Object Variable or With block variable not set

on the second last line:

rKill.EntireRow.Delete

I am thinking because I dim rKill as range, and I am trying to set rows into the range which make this error, but I tried this:

Set rKill = Rows (a). Range
Else
Set rKill = Union (rKill, Rows (a)).Range

but still doesn't work.

You want to specify the sheets you are using. Instead of "Rows(a)", use "Sheets("mySheet").Rows(a)" — OpiesDad

Kathara Kathara · Accepted Answer · 2016-02-12T07:52:28

Can you try this for an instance:

Sub project()

    Dim Ws As Worksheet
    Set Ws = ActiveWorkbook.ActiveSheet

    Dim a As Long
    a = 3

    Dim rKill As Range

    Do Until Ws.Cells(a, 7).Value = ""
        If (Abs(Ws.Cells(a - 1, 7).Value - Ws.Cells(a, 7).Value) > 5) And (Abs(Ws.Cells(a + 1, 7).Value - Ws.Cells(a, 7).Value) > 5) Then
            If rKill Is Nothing Then
                Set rKill = Ws.Range(Rows(a))
            End If
        End If
        a = a + 2
    Loop
    rKill.EntireRow.Delete

End sub

Excel VBA delete rows Object Variable or With block variable not set

1 Answers