3

I have an autofilter and in one of the columns there are three main types of value: an employee ID, a name or empty. I need to find the names only.

When you have a pivottable then you can loop trough the items and you can create an if statement to determine if it should be visible or not, is there nothing like that for autofilters?

The column could have employee ID like A123456 or names like Andy, Tom and so on. Because of Andy I can't filter on A*.

I can use this to get all the unique values

aa = Application.WorksheetFunction.Unique(Range("P2:P528").Value)

But when I loop through this list to set the filter it will only set the filter to this specific item, not "add to the list" as you would do manually in Excel

for i = 1 to 10
    ActiveSheet.Range("$A$1:$P$528").AutoFilter Field:=16, Criteria1:=aa(i,1)
next i

Can I make the above loop remember the previous just like when you set filters in pivot tables?

EDIT; I realize I can make a helper column with =isnumber(mid(P2,2,6)) but because I don't know the size of the table (number of columns may vary) I try to avoid this.

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • 1
    You can feed an array to an Autofilter and filter on values [as seen here](https://stackoverflow.com/questions/38404176/using-string-array-as-criteria-in-vba-autofilter) . Just create your array by checking if it's an "A-number" and only adding the values that are not. – Notus_Panda Apr 26 '23 at 09:23

1 Answers1

4

Autofilter Using the Keys of a Dictionary (an Array) of Values

enter image description here

Sub FilterNames()
    
    With ActiveSheet ' improve!
        
        If .FilterMode Then .ShowAllData
        If .AutoFilterMode Then .AutoFilterMode = False
        
        With .Range("A1").CurrentRegion
            
            Dim rCount As Long: rCount = .Rows.Count - 1
            Dim Data: Data = .Columns(16).Resize(rCount).Offset(1).Value
            
            Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
            dict.CompareMode = vbTextCompare
            
            Dim r As Long, rStr As String
            
            For r = 1 To rCount
                rStr = CStr(Data(r, 1))
                If Len(rStr) > 0 Then ' is not blank
                    If Not Mid(rStr, 2, 1) Like "#" Then ' 2nd char. is no digit
                        dict(rStr) = Empty
                    End If
                End If
            Next r
            
            .AutoFilter 16, dict.keys, xlFilterValues
        
        End With
    
    End With

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • I have not tested this yet but it looks great. I tried to use a dictionary to filter on but that was the wrong data type, using dictionary keys is very clever! I believe the only thing I need to edit is to just get the unique values and the count of Unique values in rCount. There are a lot of duplicates and the dictionary won't like duplicates. Thank you very much! – Andreas Apr 26 '23 at 10:11
  • 1
    This shouldn't be an issue with how VBasic wrote the code, your values (in the dict) don't matter and you're not using dict.Add so there's no issue with duplicates. – Notus_Panda Apr 26 '23 at 10:40
  • True, didn't think about that. But in this case there are 528 lines and only 40-ish unique values. It might not be more efficient to extract the unique values but it will be easier when I debug the code that it only does one value once. – Andreas Apr 26 '23 at 12:04