VBA to Delete the Filtered Rows in an Excel Table

Jerry Paladino
Jerry Paladino used Ask the Experts™
on
Requesting help with the VBA to delete the filtered rows from an Excel Table.   I do not want to delete the entire row but only the row in the table.   I understand the code below will delete the hidden table rows but it also deletes the entire row which I want to avoid.  Only the table row should be deleted.

    For Each rw In [Tbl_Mstr].Rows
        If rw.Rows.Hidden Then
           rw.Rows.Delete
        End If
    Next rw

Open in new window

Sample file with a filtered table is attached.  The Table name is "Tbl_Mstr".

Thank You - Jerry
Filtered-Table.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,
pls try
Sub Macro()
Set Rng = Nothing
Set tbl = ActiveSheet.ListObjects("Tbl_Mstr")
lastrow = tbl.ListRows.Count
    For Idx = lastrow To 1 Step -1
        Set lr = tbl.ListRows(Idx)
        If lr.Range.EntireRow.Hidden = True Then
            If Rng Is Nothing Then
                Set Rng = lr.Range
            Else
                Set Rng = Union(Rng, lr.Range)
            End If
        End If
    Next
    If Not Rng Is Nothing Then
        Range("a1").CurrentRegion.AutoFilter
        Rng.Delete shift:=xlShiftUp
        Range("a1").CurrentRegion.AutoFilter
    End If
End Sub

Open in new window

Regards
Rgonzo1971,

Works great!  I appreciate your help.

Thank You - Jerry
Rgonzo1971,

After working with this for a bit, may I suggest replacing the lines that reference Range("A1") with a table reference in the event the table being acted upon is located elsewhere on the worksheet.
    If Not rng Is Nothing Then
        [Tbl_Mstr[#Headers]].CurrentRegion.AutoFilter
        rng.Delete shift:=xlShiftUp
        [Tbl_Mstr[#Headers]].CurrentRegion.AutoFilter
    End If

Open in new window

Or, alternately...
    If Not rng Is Nothing Then
        Range("Tbl_Mstr[#Headers]").CurrentRegion.AutoFilter
        rng.Delete shift:=xlShiftUp
        Range("Tbl_Mstr[#Headers]").CurrentRegion.AutoFilter
    End If

Open in new window

Again - Thank you for your help!
Jerry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial