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


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext