Macro VBA Code to delete filtered rows in a list

[Sc7L]

I've recorded Macro to delete irrelevant data/rows in a list & only required rows with "CONSUMED", however, it only managed to filter irrelevant rows & unable to delete it.

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.ListObjects("Table17").Range.AutoFilter Field:=7, Criteria1:= _
"<>*CONSUMED*", Operator:=xlAnd
Selection.EntireRow.Delete
ActiveSheet.ListObjects("Table17").Range.AutoFilter Field:=7
End Sub

 

[saboh12617]

Hello,

If i understood correctly, the following code should work.

Be careful however, as if there are other information on the rows deleted outside of your table, they will be deleted as well.

Sub Macro1()

  Application.DisplayAlerts = False
  With ActiveSheet.ListObjects("Table17")
    .Range.AutoFilter Field:=7, Criteria1:="=*CONSUMED*", Operator:=xlAnd
    .DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    .Range.AutoFilter Field:=7
  End With
  Application.DisplayAlerts = True
End Sub