We help IT Professionals succeed at work.

VBA to Delete the Filtered Rows in an Excel Table

289 Views
Last Modified: 2017-03-10
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

CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Rgonzo1971,

Works great!  I appreciate your help.

Thank You - Jerry
CERTIFIED EXPERT

Author

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