CODEWITHSUNDEEP
excelvba
GoldStandard
GoldStandard

Reputation: 69

Excel advanced filter for uniques results in one duplicated entry

My dataset starts with a bunch of blank rows before it reaches the first chunk of filled cells. In Column B (shifts to column C after the macro is run) I have an Officer name, followed by a bunch of blank rows, followed by another officer name, etc. It is possible this column has duplicate officer names.

I tried writing an advanced filter macro to copy the list of unique officer names into cell A1.

Private Sub UserForm_Initialize()

Columns("A:A").Insert 'inserts column to left of A for officer names to be pasted in


With Range("c1:c" & Cells(Rows.Count, 3).End(xlUp).Row)
.AdvancedFilter Action:=xlFilterCopy, copytorange:=Cells(2, 1), unique:=True
On Error Resume Next
End With

End Sub

But my result is this:
Officer A
(blank row)
Officer A
Officer B
Officer C
etc....

Since this result is the same as if I did an advanced filter without VBA, I think it's the way my raw data is formatted. I tried manually adding a header to Officer Name column without any luck.

How can I get my results to show only the list of non-duplicated names?

Upvotes: 1

Views: 3037

Answers (2)

Daniel McCracken
Daniel McCracken

Reputation: 494

You could just use the "RemoveDuplicates" method instead.

With ActiveSheet
    .Columns("A").Insert
    .Range("C1:C" & .Cells(.Rows.Count, 3).End(xlUp).Row).Copy
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    Application.CutCopyMode = False
End With

Upvotes: 1

gordon613
gordon613

Reputation: 2952

Your code is great. It is just that the top row is treated as a header row, that is the reason you see Officer A twice and no other officer twice.

I recommend to make your own header row such as "Officer Name", and incorporate that in the filter. You will then see this duplicated in the second column and you can ignore it.

You can delete the blank rows by using code such as 

ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Upvotes: 1

Related Questions