Delete Row From Table based on values in table column

Doug Mutzig

Board Regular
Joined
Jan 1, 2019
Messages
57
Office Version
  1. 365
Platform
  1. Windows
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 :
Code:
[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:
Code:
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
Code:
 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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top