Delete Row From Table based on values in table column

[Doug Mutzig]

Good morning all!

I have a table (T_Staff) with 20 columns (the first 5 are hidden). The 15th column is titled Active and has data validation to allow either Yes or No meaning either the staff is active or is not active. I would like to have a button that when clicked will delete the table rows that have a No in the Active column. I did some digging and found the post and code: https://www.mrexcel.com/forum/excel...ete-row-table.html?highlight=delete+table+row

Original code by DanteAmor :

[COLOR=#333333]Sub Macro1()[/COLOR]    Application.ScreenUpdating = False
    Set h = Sheets("unsurprisingly")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    u = h.Range("K" & Rows.Count).End(xlUp).Row
    h.Range("A6:K" & u).AutoFilter Field:=11, Criteria1:="Closed"
    Rows("7:" & u).Delete Shift:=xlUp
    If h.AutoFilterMode Then h.AutoFilterMode = False
    Application.ScreenUpdating = True [COLOR=#333333]End Sub[/COLOR]

I have modified the code for my table with the following:

Sub StaffRemoveInactive()


    Application.ScreenUpdating = False
    Set h = Sheets("Staff")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    u = h.Range("P" & Rows.Count).End(xlUp).Row
    h.Range("C12:P" & u).AutoFilter Field:=15, Criteria1:="No"
    Rows("12:" & u).Delete Shift:=xlUp
    If h.AutoFilterMode Then h.AutoFilterMode = False
    Application.ScreenUpdating = True
    
End Sub

however I am hitting an error on the line

 Rows("12:" & u).Delete Shift:=xlUp

My table headers are on row 11 so I wanted it to start at row 12. Does anyone have an idea of what I have done wrong on this?

Thank you very much for your help and guidance!
Doug

 

[KennyGreens]

Hi, what error are you getting?

 

[Doug Mutzig]

Hi! Sorry I should have included that.

Run-time error '1004' This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet.

I was testing and adjusted my h.Range from C12:P to B11:P as that is the left most corner of the table, but still received the same error.

 

[DanteAmor]

Sorry for being so late.

You can use the columns from A to P.
Change 16 to the column number that contains "yes / no", in this case 16 is for column P.

Sub StaffRemoveInactive()
  Dim h As Worksheet, u As Long
    Application.ScreenUpdating = False
    Set h = Sheets("Staff")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    u = h.Range("P" & Rows.Count).End(xlUp).Row
    h.Range("[COLOR=#ff0000]A12:P"[/COLOR] & u).AutoFilter Field:=[COLOR=#ff0000]16[/COLOR], Criteria1:="No"
    h.AutoFilter.Range.Offset(1).EntireRow.Delete
    h.ShowAllData
    Application.ScreenUpdating = True
End Sub