error 1004 method intersect of object _global failed

[gorgkhan]

I want to delete the cells selected in the table. Error occurs when I Delete a mid row of the table.

Sub DeleteTableMember()
Application.ScreenUpdating = False
On Error Resume Next
If ActiveSheet.ListObjects(1) Is Nothing Then Exit Sub
If ActiveSheet.ListObjects(1).DataBodyRange Is Nothing Then Exit Sub
Dim COLUMN_FIRSTCOLUMN As Integer
COLUMN_FIRSTCOLUMN = ActiveSheet.ListObjects(1).DataBodyRange(1, 1).Column
On Error GoTo 0

    Dim tmpCell As Range
    Dim delRng As Range
    For Each tmpCell In Selection
        If Not Intersect(tmpCell, ActiveSheet.ListObjects(1).DataBodyRange) Is Nothing Then
            If delRng Is Nothing Then
                Set delRng = Cells(tmpCell.Row, COLUMN_FIRSTCOLUMN)
            Else
                Set delRng = Union(Cells(tmpCell.Row, COLUMN_FIRSTCOLUMN), delRng)
            End If
        End If
    Next tmpCell
    If delRng Is Nothing Then Exit Sub
    Dim i As Integer
    For i = ActiveSheet.ListObjects(1).ListRows.Count To 1 Step -1
        If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then
            ActiveSheet.ListObjects(1).ListRows(i).Delete
        End If
    Next i

Application.ScreenUpdating = True
End Sub

any help/fix/improvement is appriciated!

 

[rlv01]

Which line of code generates the error?

 

[gorgkhan]

Which line of code generates the error?

as the title explained , this line

If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then

after the deletion, the next iteration to intersect causes that error

 

[MARK858]

as the title explained , this line

Actually it didn't

When you get the error and you hover your mouse over i and delRng in the line what values do you get?

 

[rlv01]

as the title explained , this line

Umm, not really. The error message pertains to the Intersect statement, and you have two such in your code.

I think what is happening is that once you delete the row, variable delRng goes to an undefined state and throws an error on the next pass. You many need to consider adding an Exit For statement or some other means to handle it.

    For i = ActiveSheet.ListObjects(1).ListRows.Count To 1 Step -1
        If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then
            ActiveSheet.ListObjects(1).ListRows(i).Delete
            Exit For  '<-- ADD
        End If
    Next i

 

[gorgkhan]

Actually it didn't

When you get the error and you hover your mouse over i and delRng in the line what values do you get?

I get i=3 and nothing is displayed for the delRng

 

[gorgkhan]

Umm, not really. The error message pertains to the Intersect statement, and you have two such in your code.

I think what is happening is that once you delete the row, variable delRng goes to an undefined state and throws an error on the next pass. You many need to consider adding an Exit For statement or some other means to handle it.

    For i = ActiveSheet.ListObjects(1).ListRows.Count To 1 Step -1
        If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then
            ActiveSheet.ListObjects(1).ListRows(i).Delete
            Exit For  '<-- ADD
        End If
    Next i

Yes, you are right! I got an error trying getting delRng.address after the deletion. But I don't want to terminate for as I may have more than 1 cell to be deleted. Why delRng got affected by that statement?

 

[rlv01]

Why delRng got affected by that statement?

Because you deleted the object (the table row) and the variable had nothing to reference to, hence the error.

ut I don't want to terminate for as I may have more than 1 cell to be deleted.

Something like this then:

    Dim RCnt As Long
    For i = ActiveSheet.ListObjects(1).ListRows.Count To 1 Step -1
        If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then
            ActiveSheet.ListObjects(1).ListRows(i).Delete
            If RCnt = 1 Then
                Exit For
            Else
                RCnt = delRng.Rows.Count
            End If
        End If
    Next i

 

[gorgkhan]

Because you deleted the object (the table row) and the variable had nothing to reference to, hence the error.

My opinion was delRng referenceing to a normal range, and it should remain intact after deleteing the table row!

Something like this then:

I don't understand the use of RCnt and it is not initialized therefore the if condition to check its value is useless.



Anyway I declare a string variable and initialized by haveing the delRng.address as its value!

 

[rlv01]

My opinion was delRng referenceing to a normal range, and it should remain intact after deleteing the table row!

"Should" is a dangerous word in coding because it often describes the way we want something to work instead of the way it actually does work.

I don't understand the use of RCnt and it is not initialized therefore the if condition to check its value is useless.

In VBA, most variables have a default initialization value based on their type. For type Long, it is zero, which works well here as long asdelRng has more than one row. For the case where delRng is just one row, you're right that we will need an additional RCnt = delRng.Rows.Count before the loop:

    If delRng Is Nothing Then Exit Sub
    Dim i As Integer
    Dim RCnt As Long
    RCnt = delRng.Rows.Count
    For i = ActiveSheet.ListObjects(1).ListRows.Count To 1 Step -1
        If Not Intersect(ActiveSheet.ListObjects(1).DataBodyRange(i, 1), delRng) Is Nothing Then
            ActiveSheet.ListObjects(1).ListRows(i).Delete
            If RCnt = 1 Then
                Exit For
            Else
                RCnt = delRng.Rows.Count
            End If
        End If
    Next i