CODEWITHSUNDEEP
excelvbaautofilterexcel-tableslistobject
Kyle Cranfill
Kyle Cranfill

Reputation: 77

deleting specific rows from a table

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

Upvotes: 1

Views: 71

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

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

Upvotes: 2

NoobVB
NoobVB

Reputation: 1033

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

Upvotes: 1

Related Questions