VBA Autofilter to exclude text from list

[kiwiDD]

Hi All

Just wondering if the below code can be modified to exclude certain text from a filtered list as opposed to having to list all the text that you do want selected?

Current code which will select C:

ActiveSheet.Range("$A$1:$AA$11").AutoFilter Field:=3, Criteria1:="C"


Cheers,

KiwiDD

 

[AC PORTA VIA]

Try this

Sub FILTERALL_BUT() 'FILTER ALL BUT "C"
Dim arr1(11) As String
Dim i As Integer
i = 1
'Populate array with  values you don't want to see
    For Each cell In Range("C2:C11")
      If cell <> "C" Then
        arr1(i) = cell
        i = i + 1
      End If
    Next
'Filter on array values
    Range("C2:C11").AutoFilter Field:=3, _
            Criteria1:=Array(arr1()), Operator:=xlFilterValues
End Sub

 

[Akuini]

Try:

ActiveSheet.Range("$A$1:$AA$11").AutoFilter Field:=3, Criteria1:="<>C"

but at most you can only use 2 criteria .

 

[Fluff]

You could also put a formula in AB1:AB11 that returns (for instance) true or false depending on whether col C has a value to be filtered & then filter on col AB

 

[kiwiDD]

Thanks all, the only thing that can work for me in the end is the true or false option as I have more than 2 criteria.

 

[Peter_SSs]

If you don't have a huge number of rows (your example has only 11) you could try this code that doesn't require a helper column. If the number of rows is very large this could become a little slow but an alternative code could be arranged.
This could also be made more flexible if the number of rows can vary. If you need that, tell us which column, if any, can reliably be used to determine the last row of data.

Sub AutoFilterExcludingCertainValues()
  Dim c As Range
  Dim s As String
  
  Const ValsToExclude As String = "|C|T|P|"  '<- Edit this list as required
  
  For Each c In Range("C2:C11")
    If InStr(ValsToExclude, "|" & c.Value & "|") = 0 Then s = s & "," & c.Value
  Next c
  Range("$A$1:$AA$11").AutoFilter Field:=3, Criteria1:=Split(Mid(s, 2), ","), Operator:=xlFilterValues
End Sub