Advanced Filter VBA problem

[ianfrancis56]

Hi all,

I am having a problem with Advanced Filter in VBA.

Sub DataMineTest()
Dim Tgt As Range

    With Sheets(1)
    last = .Range("A2").End(xlDown).Row
    Set Tgt = Sheets(2).Range("A2:A" & Sheets(2).Rows.End(xlDown).Row)
    
        .Range("A2:A" & last).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Tgt, Unique:=True
    End With
    
End Sub

Column A in Sheets(1) contains position names. I want Advanced Filter to only show the unique values, no duplicates. However, there always seems to be a duplicate.

The example list in Sheets(1) Column A is
Service Support Engineer
Solutions Architect
Project Manager
Solutions Architect
Project Manager
Service Assurance Engineer
Service Automation Engineer
Project Manager
Service Assurance Engineer
Service Automation Engineer
Project Manager
Project Manager
Service Assurance Engineer
Service Automation Engineer
Project Manager
Service Support Engineer
Service Support Engineer
Service Support Engineer
Service Support Engineer
Service Support Engineer

The Advanced Filter results are

Service Support Engineer
Solutions Architect
Project Manager
Service Assurance Engineer
Service Automation Engineer
Service Support Engineer

Any suggestions? Thanks!

 

[AlphaFrog]

When you do an Advanced filter, the top cell is always considered a header and not part of the Uinque results.

So in this case...

        .Range("A2:A" & last).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Tgt, Unique:=True

A2 is the header and may have a diplicate listed below it.

Try something like this...

 .Range("A1:A" & last).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
 .Range("A2:A" & last).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(2).Range("A2")

 

[ianfrancis56]

Perfect thanks!

 

[yaseensallam]

Thanks