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!

 

[gorgkhan]

"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.



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

what's RCnt used for? It's not related to my problem I guess

 

[gorgkhan]

thanks everybody for your time and helping me!!

 

[rlv01]

what's RCnt used for? It's not related to my problem I guess

I get the impression that you have only looked at the code I posted and not actually tried it, because RCnt is very much related to the solution to your problem.