Delete Table row based on cell value

Hi All

Well I tried a few solutions and only would up making more mistakes.

I need a function that will delete rows from a table I have on a worksheet.  Basically if a cell from B2 down to B-whatever has the word "Delete" or "Delete2" in it I would like the that whole row deleted.  Note I don't want the worksheet row deleted, only the row in the excel table.  The data goes from B over to S.
deskchainsAsked:
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.

dlmilleCommented:
This one was a bit tricky and took a bit more time to develop than I originally thought.  It turns out you can't delete listRows from a list object if the data is filtered.  I assume you are working with a list object as you call it a "table".  If not, please advise and I'll recode, accordingly.

Here's your code.  Note line 17 specifies the name of your table:
Option Explicit

Sub delRowsInTable()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim lRow As Long
Dim loTable As ListObject
Dim rDelete As Range

    Application.ScreenUpdating = False
    
    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    If wks.AutoFilterMode Then wks.AutoFilterMode = False
    Set loTable = wks.ListObjects("Table1")
    loTable.Range.AutoFilter field:=1, Criteria1:= _
        "=Delete", Operator:=xlOr, Criteria2:="=Delete2"
    
    Set rDelete = loTable.Range.Offset(1, 0).Resize(loTable.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    
    loTable.Range.AutoFilter
    
    rDelete.Delete shift:=xlUp

    loTable.Range.AutoFilter
    
    Application.ScreenUpdating = True
End Sub

Open in new window


See attached.

Cheers,

Dave
delTableRow-r1v1.xls
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
deskchainsAuthor Commented:
Viola...wow definitely not the direction I was headed, but it works great!

Thanks much for your help, great job!
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
Microsoft Excel

From novice to tech pro — start learning today.