Deleting Unioned Range Error

Question

I am getting the error "Object or With block variable not set" on the rng_select.Delete line. I tried Debug.Print.rng_select.address with no luck. My sheet is activated - any ideas?

Sub Delete()

Dim wkb As Workbook
Dim Command As Worksheet, Data As Worksheet, Transfer AsWorksheet, Accredited As Worksheet, FollowUp As Worksheet
Dim LcellData As Long, LcellTransfer As Long, LcellFollowUp As Long, LcellAccredited As Long, a As Long, b As Long
Dim rng_select As Range


Set wkb = Application.ActiveWorkbook
Set Command = wkb.Sheets("Command")
Set Data = wkb.Sheets("Data")
Set Transfer = wkb.Sheets("Transfers")
Set FollowUp = wkb.Sheets("FollowUp")
Set Accredited = wkb.Sheets("Accredited")

LcellFollowUp = FollowUp.Cells(Rows.Count, "A").End(xlUp).Row

FollowUp.Activate

For a = LcellFollowUp To 2 Step -1
   If Cells(a, 8).Value = Cells(a - 1, 8).Value Then
        If rng_select Is Nothing Then
            Set rng_select = Cells(a, 1).EntireRow
        Else
            Set rng_select = Union(rng_select, Cells(a, 1).EntireRow)
        End If
   End If
Next a

rng_select.Delete

Exit Sub

Answer 1

I am getting the error "Object or With block variable not set" on the rng_select.Delete line

You are getting that error because rng_select is nothing. And that is probably because the code never entered inside the If/EndIF inside the For loop

To check, change this rng_select.Delete to

If Not rng_select Is Nothing Then
    rng_select.Delete
Else
    MsgBox "Range not valid"
End If

Also, avoid the use of .Activate. You may want to see This

Your code can be written as

Sub Delete()
    Dim wkb As Workbook
    Dim Command As Worksheet, Data As Worksheet, Transfer As Worksheet
    Dim Accredited As Worksheet, FollowUp As Worksheet
    Dim LcellData As Long, LcellTransfer As Long, LcellFollowUp As Long
    Dim LcellAccredited As Long, a As Long, b As Long
    Dim rng_select As Range

    Set wkb = Application.ActiveWorkbook
    Set Command = wkb.Sheets("Command")
    Set Data = wkb.Sheets("Data")
    Set Transfer = wkb.Sheets("Transfers")
    Set FollowUp = wkb.Sheets("FollowUp")
    Set Accredited = wkb.Sheets("Accredited")

    With FollowUp
        LcellFollowUp = FollowUp.Range("A" & .Rows.Count).End(xlUp).Row

        For a = 2 to LcellFollowUp '<~~ No need for reverse loop
           If .Cells(a, 8).Value = .Cells(a - 1, 8).Value Then
                If rng_select Is Nothing Then
                    Set rng_select = .Cells(a, 1).EntireRow
                Else
                    Set rng_select = Union(rng_select, .Cells(a, 1).EntireRow)
                End If
           End If
        Next a
    End With

    If Not rng_select Is Nothing Then
        rng_select.Delete
    Else
        MsgBox "Range not valid. No Matching range found"
    End If
End Sub