Excel VBA: Loop through autofilter problem

[alantse2010]

Hi all,
I want to loop through autofilter criteria in column G( below photo) and do something, such as copy or sum.

How can i loop each choice in VBA?

Thank you very much for your help.

 

[kevin9999]

I note you have 365 indicated on your account profile, so how about something like this?

Option Explicit
Sub Test()
    Dim rng As Range, a, i As Long
    Set rng = Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
    
    a = Application.Sort(WorksheetFunction.Unique(rng))
    For i = LBound(a) To UBound(a)
    
        'Do something with each value here...
    
    Next i

End Sub

 

[alantse2010]

I note you have 365 indicated on your account profile, so how about something like this?

Option Explicit
Sub Test()
    Dim rng As Range, a, i As Long
    Set rng = Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
   
    a = Application.Sort(WorksheetFunction.Unique(rng))
    For i = LBound(a) To UBound(a)
   
        'Do something with each value here...
   
    Next i

End Sub

Thank you very much for your reply, that i want something like this but it cause the error
"must be array"

how to fix it?

 

[kevin9999]

It works for me, but I don't know how you're trying to use it. Could you post the full code you're attempting please?

 

[alantse2010]

It works for me, but I don't know how you're trying to use it. Could you post the full code you're attempting please?

I try your code again, it works now.
I don't know why the previous will cause an error.
Anyway, thank you very much for your help.

 

[kevin9999]

Happy to help, and thanks for the feedback