Advanced Filter

CopyToRange

Dim rgeCriteria As Range 
Dim rgeExtract As Range 

Set rgeCriteria = Range("A2:A4") 
Set rgeExtract = Range("C2") 
Range("B2").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ 
                                         CriteriaRange:=rgeCriteria, _ 
                                         CopyToRange:=rgeExtract 

Getting a List of Unique Items

Here are four examples of counting unique values in a list. Each of these examples creates an array of the unique items, so they can be modified to to those arrays for a purpose other than just counting the unique items.

Sub cMethodAdvFilter() 
    CountUniqueByAdvFilter Selection.Address 
End Sub 

Sub CountUniqueByAdvFilter(mRange As String) 
    Dim TheRange As String 
    Application.ScreenUpdating = False 
    
    TheRange = "'[" & ActiveWorkbook.Name & _  
    "]" & ActiveSheet.Name & "'!" & mRange  
    
    Workbooks.Add 
    
    Range(TheRange).AdvancedFilter Action:=xlFilterCopy, CopyToRange _ 
:=Range("A1"), Unique:=True 
    
    MsgBox Application.WorksheetFunction.CountA(Range("A:A")) 
    
    ActiveWorkbook.Close False 
    
    Application.ScreenUpdating = True 
End Sub