Applying auto filter to A8 on existing code(Wild card unable to use in autofilter array answered by RickXL)

[yun_h]

Hi Experts,

I found below code in forums while I was looking for way to use wild card to filter. Currently, it works fine but I want to switch the filtered row to A8 instead of A1.

With my lack of knowledge, I thought I could change .Columns("A:A") to .Columns("A8:A"). But I get debug.. is there anyway that I can change the filter row without changing below code too much?

Thanks in advance !

Sub myFilter()
    Dim dic     As Object
    Dim eleData As Variant
    Dim eleCrit As Variant
    Dim arrData As Variant
    Dim vTst    As Variant
  
    Set dic = CreateObject("Scripting.Dictionary")
    vTst = Array("*ABC*", "*DEF*", "*GHI*", "*JKLM*", "*nn*")
    With ActiveSheet
        .AutoFilterMode = False
        arrData = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each eleCrit In vTst
            For Each eleData In arrData
                If eleData Like eleCrit Then dic(eleData) = vbNullString
            Next
        Next
        .Columns("A:A").AutoFilter Field:=1, Criteria1:=dic.Keys, Operator:=xlFilterValues
    End With
End Sub

 

[kevin9999]

Please post a copy of your worksheet using the XL2BB - Excel Range to BBCode or alternatively, share you file via Google Drive, Dropbox or similar file sharing platform. You can easily disguise any data that may be sensitive.