asked on

VBA to Delete the Filtered Rows in an Excel Table

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

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jerry Paladino

ASKER

Rgonzo1971,

Works great! I appreciate your help.

Thank You - Jerry

Jerry Paladino

ASKER

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