VBA delete rows based on cell criteria

[kwg]

I am trying to create code for a table to filter on all in the filter box list but one item, delete rows that are visible, across multiple rows in the workbook, (my sheets in excel contain over 5000 rows). the code below works but I need it to delete rows NOT equal to Product25. I am using a table with over 6000 lines and over 50 items to select from on the filter column


Option Explicit

Sub Delete_Rows_Based_On_Value_Table()
'Apply a filter to a Table and delete visible rows

Dim lo As ListObject

'Set reference to the sheet and Table.
Set lo = Sheet3.ListObjects(1)
lo.Parent.Activate 'Activate sheet that Table is on.

'Clear any existing filters
lo.AutoFilter.ShowAllData

'1. Apply Filter
lo.Range.AutoFilter Field:=4, Criteria1:="Product25"


'2. Delete Rows
Application.DisplayAlerts = False
lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

'3. Clear Filter
lo.AutoFilter.ShowAllData

End Sub

 

[rlv01]

I need it to delete rows NOT equal to Product25.

   lo.Range.AutoFilter Field:=4, Criteria1:="<>Product25"

 

[kwg]

thanks but getting a run time error 424, Object required

 

[rlv01]

I tried it on a listobject I created and it worked. Generally speaking if If this works (for your orginal code)

lo.Range.AutoFilter Field:=4, Criteria1:="Product25"

then this should also work

 lo.Range.AutoFilter Field:=4, Criteria1:="<>Product25"

The 424 runtime error suggests something is amiss for your 'lo' object declaration. Try using the debugger to single step though your original unmodified code as posted in #1 and see if you can execute it without errors. I you can then change the criteria to "<>Product25"

 lo.Range.AutoFilter Field:=4, Criteria1:="<>Product25"

And repeat the exercise. If you get an error, on which line does it happen?