VBA Advanced filtering and applying some actions on filtered data

[ineedmesome]

Hi guys,
Here's the type of data i'm talking about :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]F17XXX[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]F18XXX[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]F18XXX[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]F17XXX[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]F18XXX[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]F18XXX[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]F17XXX[/TD]
[/TR]
</tbody>[/TABLE]

So i know how to use advanced filtering to have an output range that looks like this :
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]

But what i actually nead, is having an 'internal' list in my code. Something like that : mylist = "A,B,C,D". The idea is that i wanna perform a filter on my data based on this list.
Like this

.Range("A1","B8").AutoFilter field:=7, Criteria1:="[COLOR=#ff0000]Element of my list[/COLOR]"

So the data will become :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]Element of my list[/TD]
[TD]F17XXX[/TD]
[/TR]
[TR]
[TD]Element of my list[/TD]
[TD]F17XXX[/TD]
[/TR]
</tbody>[/TABLE]

And then i can perform some data processing with conditions about the Invoice... But first i just wanna perform this first step.
Maybe it's easier to just perform an advanced filter with VBA and actually outputting the filter in a range and then looping through this range and delete it at the end ? Because i don't want any change on the actual sheet.

Thanks guys in advance for your answers!

 

[Domenic]

You can use the Dictionary object to create a unique list of clients. Then you can loop through each element/key of the dictionary like this...

Sub test()

    Dim dicClients As Object
    Dim vClients As Variant
    Dim sClient As String
    Dim i As Long
    
    Set dicClients = CreateObject("Scripting.Dictionary")
    dicClients.CompareMode = 1 'vbTextCompare
    
    vClients = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For i = LBound(vClients) To UBound(vClients)
        sClient = vClients(i, 1)
        If Not dicClients.Exists(sClient) Then
            dicClients(sClient) = ""
        End If
    Next i
    
    For i = 0 To dicClients.Count - 1
        Debug.Print dicClients.Keys()(i)
    Next i
    
    Set dicClients = Nothing
    
End Sub

Hope this helps!

 

[ineedmesome]

You can use the Dictionary object to create a unique list of clients. Then you can loop through each element/key of the dictionary like this...

Sub test()

    Dim dicClients As Object
    Dim vClients As Variant
    Dim sClient As String
    Dim i As Long
    
    Set dicClients = CreateObject("Scripting.Dictionary")
    dicClients.CompareMode = 1 'vbTextCompare
    
    vClients = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For i = LBound(vClients) To UBound(vClients)
        sClient = vClients(i, 1)
        If Not dicClients.Exists(sClient) Then
            dicClients(sClient) = ""
        End If
    Next i
    
    For i = 0 To dicClients.Count - 1
        Debug.Print dicClients.Keys()(i)
    Next i
    
    Set dicClients = Nothing
    
End Sub

Hope this helps!

Thanks sir! Yeah this definitely helped!