Avatar of Jerry Paladino
Jerry Paladino
Flag for United States of America 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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Jerry Paladino

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rgonzo1971

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Your help has saved me hundreds of hours of internet surfing.
fblack61