Deleting Autofilter row

[SteynBS]

Hi Guys. So I am using VBA to AutoFilter a row in a table with conditions. Now I need to delete the entire row. My limited experience with VBA only allows me to select the row and delete, but the row number could change every time.

Sub AutoFilterDelete()


ActiveSheet.ListObjects("VendorCertFrmFile").Range.AutoFilter Field:=2, _
Criteria1:="Appliances Medical Devices Cosmetics"
Rows("157:157").Select (This value will change every time. I need to select the entire row based on the criteria.)
Selection.Delete Shift:=xlUp

End Sub

 

[kevin9999]

Try this

Sub AutoFilterDelete()
    With ActiveSheet.ListObjects("VendorCertFrmFile").Range
        .AutoFilter 2, "Appliances Medical Devices Cosmetics"
        .Offset(1).EntireRow.Delete
    End With
    
    ActiveSheet.ListObjects("VendorCertFrmFile").AutoFilter.ShowAllData
End Sub

 

[Alex Blakenburg]

Or this:

Sub AutoFilterDelete()

Dim loVendor As ListObject
Dim rngDelete As Range

Set loVendor = ActiveSheet.ListObjects("VendorCertFrmFile")

With loVendor
    .Range.AutoFilter Field:=2, Criteria1:="Appliances Medical Devices Cosmetics"
    Set rngDelete = .ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible)
    .AutoFilter.ShowAllData
    rngDelete.Delete Shift:=xlUp
End With

End Sub

 

[SteynBS]

Or this:

Sub AutoFilterDelete()

Dim loVendor As ListObject
Dim rngDelete As Range

Set loVendor = ActiveSheet.ListObjects("VendorCertFrmFile")

With loVendor
    .Range.AutoFilter Field:=2, Criteria1:="Appliances Medical Devices Cosmetics"
    Set rngDelete = .ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeVisible)
    .AutoFilter.ShowAllData
    rngDelete.Delete Shift:=xlUp
End With

End Sub

Thank you

 

[SteynBS]

Try this

Sub AutoFilterDelete()
    With ActiveSheet.ListObjects("VendorCertFrmFile").Range
        .AutoFilter 2, "Appliances Medical Devices Cosmetics"
        .Offset(1).EntireRow.Delete
    End With
   
    ActiveSheet.ListObjects("VendorCertFrmFile").AutoFilter.ShowAllData
End Sub

Thanks