VBA - using code to filter criteria NOT to include in a filter...

[Panthers0027]

Hello,

I use Office 2007.

I have a table, that I'd like to filter in my code. I'm trying to filter out certain names. Using the Macro Recorder I get this:

 ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=3, Criteria1:= _
        Array("Name1", "Name2", "Name3"), Operator:=xlFilterValues

The table has many more names, I just shortened it for this question. I'd like to know how I can simply filter out certain names. i.e. I want to take out "Sponsor" & "Sponsor Dept" as the names.

How can I do this?

Thanks for any help that you can provide!
Andre

 

[Pranesh]

Hi mmurrietta,

Thank you for your response. I tried it but it is not working correctly Any suggestions?

 

[mmurrietta]

Hi mmurrietta,

Thank you for your response. I tried it but it is not working correctly Any suggestions?

Please include your code and I'm sure we can get it working

 

[Pranesh]

Please include your code and I'm sure we can get it working

Hi,

Sorry for the delay in response.

Please find below code which I use to filter column J. I want everything to filter in column J except the ones which I listed in secondarray.

Sub fil()
    Dim filterCriteria() As String
    Dim count As Long, secondArray As Variant
    Dim L As Long, c As String, k As String, rowNumb As Long
   
    secondArray = Array("AZ", " AB", " ME", " NO", " JA", " AT", " AM", " GS", " SA", "MY", "CR", "PA", "PF", "ME", _
    "AL", "BI", "ZO", "CE", "GE", "FI", "TA", "BE", "WR", "EN", "BA")
   
    c = 0
    k = 0
    count = 0
    rowNumb = ActiveSheet.Range(ActiveSheet.Range("J2"), ActiveSheet.Range("J1").End(xlDown)).Rows.count
   
    For L = 1 To rowNumb
        c = ActiveSheet.Range("J1").Offset(L)
        If c <> k Then
            'check the current activity type against the array of types we don’t want. If it isn’t in the array we add it to an array that will be used as the filter criteria
            If UBound(Filter(secondArray, c)) = -1 Then
                ReDim Preserve filterCriteria(0 To count)
                filterCriteria(count) = c
                count = count + 1
            End If
 
            k = c
        End If
    Next
   
    With ActiveSheet
       ' .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter field:=10, Criteria1:=filterCriteria, Operator:=xlFilterValues
        .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter field:=10, Criteria1:=(filterCriteria), Operator:=xlFilterValues
    End With
End Sub

 

[Pranesh]

Hi mmurrietta,

Did you had time to look into the above code.?

 

[Pranesh]

Can anyone help me to solve this issue?

Thanks in advance for your help!!

 

[Pranesh]

Excel Experts!!!

Can anyone solve this issue. Im keep on surfing many more websites and this kind of issue is not solved. Hope here you will find a solution to this filter.

 

[mmurrietta]

Hi Parnuvins,

I ran your code on some mocked up data that filled in range("A1:K17") and had some two letter acronyms in column J, some of which were in your secondArray variable. It ran fine as is. I did do some modifications that you can try just in case but I really don't see any reason why it should work for a smaller set of values in your secondArray variable and then suddenly not work for a larger set of values. I commented out some of your code so you can see what functionality the code I introduced substitutes:

Sub Alternative()
    'Dim filterCriteria() as string
    Dim filterCriteria, secondArray
    Dim L As Long, c As String, k As String, rowNumb As Long ',count as Long
   
    secondArray = Array("AZ", " AB", " ME", " NO", " JA", " AT", " AM", " GS", " SA", "MY", "CR", "PA", "PF", "ME", _
    "AL", "BI", "ZO", "CE", "GE", "FI", "TA", "BE", "WR", "EN", "BA")
   
'    c = 0
'    k = 0
'    count = 0
    filterCriteria = ""
    ActiveSheet.UsedRange.Autofilter 'clear any filters before looking for the last row with the method that you have chosen.
    rowNumb = ActiveSheet.Range(ActiveSheet.Range("J2"), ActiveSheet.Range("J1").End(xlDown)).Rows.count
   
    For L = 1 To rowNumb
'        c = ActiveSheet.Range("J1").Offset(L)
'        If c <> k Then
'            'check the current activity type against the array of types we don’t want. If it isn’t in the array we add it to an array that will be used as the filter criteria
'            If UBound(Filter(secondArray, c)) = -1 Then
'                ReDim Preserve filterCriteria(0 To count)
'                filterCriteria(count) = c
'                count = count + 1
'            End If
'
'            k = c
'        End If
        c = ActiveSheet.Range("J1").Offset(L)
        If UBound(Filter(secondArray, c)) = -1 Then
            If filterCriteria = "" Then
                filterCriteria = c
            Else
                If Not (filterCriteria Like "*" & c & "*") Then
                    filterCriteria = filterCriteria & vbLf & c  'essentially we are creating a list of unique values and separating them by vbLf (linefeed)
                End If
            End If
        End If
    Next
'now we have all the values that we want to filter the range by so we turn it into an array
    filterCriteria = Split(filterCriteria, vbLf, 1 + Len(filterCriteria) - Len(Replace(filterCriteria, vbLf, "")))
   
'apply the filter, both of these lines worked fine
    With ActiveSheet
        .Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter field:=10, Criteria1:=filterCriteria, Operator:=xlFilterValues
        '.Range(.Range("A1"), .Range("A1").End(xlDown).Offset(0, 11)).AutoFilter field:=10, Criteria1:=(filterCriteria), Operator:=xlFilterValues
    End With
End Sub

I don't know everything about the situation where the error occurred but when you use that particular method for finding the last row make sure remove any filters on the sheet before doing so otherwise you may get the wrong row number. Also, in my method for turning a string into an array, you won't be able to do that with Option Explicit on unless you add some lines and to create a new string array variable and reDim it to be the appropriate size.

In any case, try this out and let me know what happens.