Delete Table Rows base on selected cells

[gberg]

I'm trying to delete Table Rows based on which cell(s) are selected. I have a macro that will select the cells for the rows I want to delete (any row with a cell value of "ICO"). I can't figure out how to delete the Table Rows for the selected cells. This is what I have but the "Selection.EntireRow.Delete" apparently does not work in a Table

Sub ico()

'declare variables
Dim ws As Worksheet
Dim SelectCells As Range
Dim xcell As Object
Set ws = Worksheets("Job Log - Client")

Range("Setup").Select

'check each cell in the specific worksheet if the criteria is matching
On Error GoTo ErrorHandler
For Each xcell In ws.UsedRange.Cells
If xcell.Value = "ICO" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xcell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xcell.Address))
End If
End If

Next

'select the cells with specified value
SelectCells.Select

Selection.EntireRow.Delete

ErrorHandler:

End Sub

 

[Fluff]

Is the ICO in a particular column? If so what is the name of that column?

 

[gberg]

"Type" is the name of the column

 

[Fluff]

Ok, how about

Sub gberg()

'declare variables
Dim ws As Worksheet
Dim SelectCells As Range, xcell As Range
Dim Tbl As ListObject

Set ws = Worksheets("Job Log - Client")
Set Tbl = ws.ListObjects("setup")

For Each xcell In Tbl.ListColumns("Type").DataBodyRange
   If xcell.Value = "ICO" Then
      If SelectCells Is Nothing Then
         Set SelectCells = xcell
      Else
         Set SelectCells = Union(SelectCells, xcell)
      End If
   End If
Next xcell

Intersect(Tbl, SelectCells.EntireRow).Delete

End Sub

In future when posting code, please use code tags. How to Post Your VBA Code

 

[igold]

This would be a generic code as I am not sure as what your table name is and what sheet it is on. This assumes "Table1" on "Sheet1"

Sub LookupTableValue()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim fc As Range
    Dim srch As String
    Dim r As Long

    Set ws = Worksheets("Sheet1")
    Set tbl = ws.ListObjects("Table1")
    srch = "ICO"
    
    For r = tbl.DataBodyRange.Rows.Count To 1 Step -1
        Set fc = tbl.DataBodyRange.Rows(r).Find(srch, LookAt:=xlWhole)
        If Not fc Is Nothing Then
            tbl.DataBodyRange.Rows(r).Delete
        End If
    Next

End Sub

 

[gberg]

Did the trick, thanks for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.