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
LVL 16
Jerry PaladinoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry PaladinoAuthor Commented:
Rgonzo1971,

Works great!  I appreciate your help.

Thank You - Jerry
0
Jerry PaladinoAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.