Delete Filtered Rows

[Jones1413]

Hello,

I have a macro that auto filters my criteria in Column I. Once that is filtered, I want to delete all of the hidden rows that are not included in on the filter. I do not want to create a loop because it is a large data set and will take too long to loop through each line. Is there anyway to make this faster to auto delete all rows that are not showing after I apply the filter?

 

[Fluff]

Are you filter criteria always the same & if so what are they?

 

[Jones1413]

Yes, my criteria is always the same. I use the array below for my auto filter:

Once it is filtered, I wanted to delete every row that doesn't contain the filtered content. Again, it is a large data set and don't want to use a loop to find the bottom of the data because it would take too long and the number of lines of data changes every day/week on the report.

With Selection
    .AutoFilter
    .AutoFilter Field:=9, Criteria1:=Array("Apples", "Oranges", "Bananas", "Grapes", _
    "Pineapple"), Operator:=xlFilterValues
    
End With

 

[Fluff]

Ok, how about

Sub Jones()
   Dim Cl As Range
   Dim Txt As String
   
   Txt = "|Apples|Oranges|Bananas|Grapes|Pineapple|"
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("I2", Range("I" & Rows.Count).End(xlUp))
         If InStr(1, Txt, "|" & Cl.Value & "|", 1) = 0 Then .Item(Cl.Value) = Empty
      Next Cl
      x = .Keys
      Range("A1:I1").AutoFilter 9, .Keys, xlFilterValues
      ActiveSheet.AutoFilter.Range.Offset(1).EntireRow.Delete
      ActiveSheet.AutoFilterMode = False
   End With
End Sub

 

[Jones1413]

Awesome. Thank you Fluff, that worked great!

Out of curiosity, what would the macro look like if my criteria changed? I do have a report that I filter based on different criteria. I have a macro that filters most of the data I need but sometimes I have to go in and manually check additional criteria to show visible on the worksheet. I would then like to have a macro auto delete the rows that are hidden.

 

[Fluff]

As long as the criteria are always an exact match just change this line to include the other values you want to keep

Txt = "|Apples|Oranges|Bananas|Grapes|Pineapple|"