Auto Filter all except......

[SueKi]

I need to get rid of rows of data where column D is not equal to SOM, MHT or ELP. My first pass was to filter for certain items in a different column, then select the range and delete those rows. That worked fine, but then more items got added and I hadn't filtered them. So, I need to be able to say filter everything except on a different column.

The below worked for my filter until I changed the criteria to a selection of items to exclude(<>). Do I have a syntax problem, or can I not do multiple exclusions like this?

Dim FilterRange As Long
FilterRange = Range("a" & Rows.Count).End(xlUp).Row
       
    Range("A1").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$L$" & FilterRange).AutoFilter Field:=4, Criteria1:=Array("<>MHT" _
        , "<>SOM", "<>ELP"), Operator:=xlFilterValues

 

[gordsky]

I need to get rid of rows of data where column D is not equal to SOM, MHT or ELP. My first pass was to filter for certain items in a different column, then select the range and delete those rows. That worked fine, but then more items got added and I hadn't filtered them. So, I need to be able to say filter everything except on a different column.

The below worked for my filter until I changed the criteria to a selection of items to exclude(<>). Do I have a syntax problem, or can I not do multiple exclusions like this?

Dim FilterRange As Long
FilterRange = Range("a" & Rows.Count).End(xlUp).Row
      
    Range("A1").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$L$" & FilterRange).AutoFilter Field:=4, Criteria1:=Array("<>MHT" _
        , "<>SOM", "<>ELP"), Operator:=xlFilterValues

I believe you can only do a maximum of 2 notequal to criteria in a filter. Advanced filters may be the way forward

 

[SueKi]

I believe you can only do a maximum of 2 notequal to criteria in a filter. Advanced filters may be the way forward

Ugh.... you seem to be right. I took it to 2, it failed and down to just one and it worked.

At least I'll stop fighting with it!

Thanks!

 

[kevin9999]

I need to get rid of rows of data where column D is not equal to SOM, MHT or ELP. My first pass was to filter for certain items in a different column, then select the range and delete those rows. That worked fine, but then more items got added and I hadn't filtered them. So, I need to be able to say filter everything except on a different column.

The below worked for my filter until I changed the criteria to a selection of items to exclude(<>). Do I have a syntax problem, or can I not do multiple exclusions like this?

Dim FilterRange As Long
FilterRange = Range("a" & Rows.Count).End(xlUp).Row
      
    Range("A1").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$L$" & FilterRange).AutoFilter Field:=4, Criteria1:=Array("<>MHT" _
        , "<>SOM", "<>ELP"), Operator:=xlFilterValues

As @gordsky pointed out, you can't use more than 2 <> in an AutoFilter, however, there is a workaround you can use without going to an advanced filter. Try the following on a copy of your data (assumes your data has headings in row 2).

Option Explicit
Sub Exclude_3()
    Dim d As Object, i As Long, c As Range, tmp As String, x
    
    Set d = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("D3", Cells(Rows.Count, "D").End(xlUp)))
    
    For i = 1 To UBound(x, 1)
    d(x(i)) = 1
    Next
    
    For Each c In Range("D3", Cells(Rows.Count, "D").End(xlUp))
        tmp = c.Value
        If d.exists(tmp) And (tmp) Like "SOM" Then d.Remove (tmp) '<< removes "SOM"
        If d.exists(tmp) And (tmp) Like "MHT" Then d.Remove (tmp) '<< removes "MHT"
        If d.exists(tmp) And (tmp) Like "ELP" Then d.Remove (tmp) '<< removes "ELP"
    Next
    
    With Range("A3").CurrentRegion
        .AutoFilter 4, Array(d.keys), 7 '<< Filters on the remaining values
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[SueKi]

As @gordsky pointed out, you can't use more than 2 <> in an AutoFilter, however, there is a workaround you can use without going to an advanced filter. Try the following on a copy of your data (assumes your data has headings in row 2).

Option Explicit
Sub Exclude_3()
    Dim d As Object, i As Long, c As Range, tmp As String, x
  
    Set d = CreateObject("scripting.dictionary")
    x = Application.Transpose(Range("D3", Cells(Rows.Count, "D").End(xlUp)))
  
    For i = 1 To UBound(x, 1)
    d(x(i)) = 1
    Next
  
    For Each c In Range("D3", Cells(Rows.Count, "D").End(xlUp))
        tmp = c.Value
        If d.exists(tmp) And (tmp) Like "SOM" Then d.Remove (tmp) '<< removes "SOM"
        If d.exists(tmp) And (tmp) Like "MHT" Then d.Remove (tmp) '<< removes "MHT"
        If d.exists(tmp) And (tmp) Like "ELP" Then d.Remove (tmp) '<< removes "ELP"
    Next
  
    With Range("A3").CurrentRegion
        .AutoFilter 4, Array(d.keys), 7 '<< Filters on the remaining values
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Wow! Thank you! Its going to take me some time to learn to understand this one!

 

[kevin9999]

Wow! Thank you! Its going to take me some time to learn to understand this one!

You're welcome and thanks for the feedback ?