deleting specific rows from a table

Question

I would like to check through a table called "Sorted_Duplicate_Removal" for errors, blanks, and values of 0 and then remove these rows from the table. Unfortunately every time I run my code:

    Dim i As Integer
For i = 2 To Worksheets("Resource Group Table").ListObjects("Sorted_Duplicate_Removal").DataBodyRange.Rows.Count + 1
    If Worksheets("Resource Group Table").Range("X" & i).Text = "#N/A" Then
        Worksheets("Resource Group Table").ListObjects("Sorted_Duplicates_Removal").ListRows(i - 1).Delete
    Else
        If Worksheets("Resource Group Table").Range("X" & i).Value = "0" Then
        Worksheets("Resource Group Table").ListObjects("Sorted_Duplicates_Removal").ListRows(i - 1).Delete
        End If
    End If
Next i

I get an error about the "out of range" on either of the lines ending in ".delete". Any information would be appreciated

I mean I guess if necessary I can convert the table back to a range and then delete the data that way, but that seems kind of silly.....so I was hoping for a better way — Kyle Cranfill, Mar 25 '22 at 21:44
Does this happen on the first iteration of the loop? When deleting rows, it's good practice to start at the end. i.e. loop backwards `i = Worksheets("Resource Group Table").ListObjects("Sorted_Duplicate_Removal").DataBodyRange.Rows.Count + 1 to 2 Step - 1`. Side note: It's usually better to [edit your question](https://stackoverflow.com/posts/71623410/edit) if you want to add information. — cybernetic.nomad, Mar 25 '22 at 21:50
There will only be 1-2 of these errors, so I'm not super worried about the efficiency of the loop, but I'll keep that back-to-front suggestion in mind. To answer your question, I've only ever made it as far as the first error cell. It will loop through the non-errors just fine but when it gets to the first error cell it enters the if statement and then throws me this "out of range" error when it gets to the line "Worksheets("Resource Group Table").ListObjects("Sorted_Duplicates_Removal").ListRows(i - 1).Delete" — Kyle Cranfill, Mar 25 '22 at 21:56

VBasic2008 · Answer 1 · 2022-04-10T07:29:06.190

Delete Filtered Rows in an Excel Table

Adjust the column appropriately: only if the table starts in column A, then the field 24 means column X. You can also use the header instead of 24, e.g. .ListColumns("Whatever").Index.

Option Explicit

Sub DeleteFilteredRows()
    
    Dim Criteria As Variant: Criteria = Array("", "0", "#N/A")
    
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Resource Group Table") _
        .ListObjects("Sorted_Duplicate_Removal")
    
    Dim dvrg As Range ' Data Visible Range
    
    With tbl
        If .ShowAutoFilter Then
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
        End If
        .Range.AutoFilter 24, Criteria, xlFilterValues
        On Error Resume Next
            Set dvrg = .DataBodyRange.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        .AutoFilter.ShowAllData
    End With
    
    If Not dvrg Is Nothing Then dvrg.Delete xlShiftUp
    
End Sub

Very helpful explanation, thanks! – Kyle Cranfill Mar 27 '22 at 00:16 — Kyle Cranfill, Mar 27 '22 at 00:16

score 1 · Accepted Answer · answered Mar 25 '22 at 22:13

i just tested this one, seems to work... Tables are a bit tricky...

the main idea is to use DataBodyRange.Rows(i - 1).Delete instead of .ListRows(i - 1).Delete

example:

Sub test()

Dim i As Integer
For i = Worksheets("Resource Group Table").ListObjects("Sorted_Duplicate_Removal").DataBodyRange.Rows.Count + 1 To 2 Step -1
    With ActiveSheet.ListObjects("Sorted_Duplicate_Removal")
    If Worksheets("Resource Group Table").Range("X" & i).Text = "#N/A" Then
        .DataBodyRange.Rows(i - 1).Delete
    Else
        If Worksheets("Resource Group Table").Range("X" & i).Value = "0" Then
        .DataBodyRange.Rows(i - 1).Delete
        End If
    End If
    End With
Next i

End Sub

deleting specific rows from a table

2 Answers2

Delete Filtered Rows in an Excel Table