Advanced Filter


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, _

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
    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

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