Get unique values with VBA (advanced filter)

Hi all,

I need to paste unique values (based on multiple criteria) from my data set to another sheet and want to use VBA in order to avoid using array formulas and slowing my file down significantly. I'm not that familiar with using advanced filtering, but that might be the best option, as it is an Excel built-in function? Open to other efficient solutions...

Sub AdvancedFilter()

    Sheets("Data").Range("A1:J119").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("Output").Range("I6:J7"), CopyToRange:=Sheets("Output").Range("F7:F30"), Unique:=True

End Sub

try

Sub test()

Sheets("Data").Range("j1", Cells(Rows.Count, 10).End(xlUp)).AdvancedFilter 2, , Sheets("Output").Range("C6"), True

   
End Sub

mohan.r1980,

This seems to ignore the 2 criteria I have...

Another way.

Sub tst()
    sn = Sheets("Data").Cells(1).CurrentRegion.Value
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(sn)
            If sn(i, 2) = Sheets("output").Range("i7") And sn(i, 6) = Sheets("output").Range("j7") Then
                If sn(i, 10) <> vbNullString Then x0 = .Item(sn(i, 10))
            End If
        Next
        Sheets("output").Range("e7").Resize(.Count) = Application.Transpose(.keys)
    End With
End Sub

bakerman2,

Thanks. Great solution! A little slow in my actual data set though. Is there a more efficient way to do this, such as Advanced Filtering?

Try this for starters.