Filter by cell value and delete rows

[Mr.Karr]

Hello,

I have a table where I need to apply filter based on a cell value. Delete rows. Can you please help with a code snippet.

Criteria: Sheets("Criteria").range("B2").value

Please see the attached file.

Thanks,
Karthik

 

[Debaser]

For example:

Sub DeleteRowsFromTable()
    With Sheets("Data").ListObjects("Table1").Range
        .AutoFilter Field:=17, Criteria1:=Sheets("Criteria").Range("B1").Value2
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        .AutoFilter Field:=17
    End With
End Sub

 

[Mr.Karr]

@Debaser, thank you very much!

 

[Marc L]

Hi !

Another way :​

Sub Demo()
    With Worksheets("Data").ListObjects(1).Range
        If Application.CountIf(.Columns(17), Range("Criteria!B1").Value) Then
            .Parent.Activate
            .AutoFilter 17, Range("Criteria!B1").Value
            .Rows("2:" & .Rows.Count).Columns(1).Select
             Selection.EntireRow.Delete
            .AutoFilter 17
            .Parent.Cells(19).Select
        End If
    End With
End Sub

​

 

[Mr.Karr]

@Debaser @Marc L Thank you guys for the response
say, if the criteria is a date, how can we modify the code, please help
appreciated

 

[Debaser]

Use:

.AutoFilter Field:=17, Criteria1:=">=" & Sheets("Criteria").Range("B1").Value2, operator:=xlAnd, criteria2:="<=" & Sheets("Criteria").Range("B1").Value2