Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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.
0
deskchains
Asked:
deskchains
1 Solution
 
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
 
deskchainsAuthor Commented:
Viola...wow definitely not the direction I was headed, but it works great!

Thanks much for your help, great job!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now