Filter/Copy Unique records

M

Mike

I have this simple code.
Range("H:H").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1"),
Unique:=True
Im trying to get a list of the Unique reords in column H.
For some reason if row H1 and H2 are the same I end up with 2 records the
same.
Example:
H1 = 249653634
H2 = 249653634
H3 = 445434212
H4 = 551569206
H5 = 551569206
H6 = 551569206
H7 = 249721825
H8 = 445531266
H9 = 249877803
H10 = 351145588
H11 = 351145588
I end up with a list that is not Unique as listed below
249653634
249653634
445434212
551569206
249721825
445531266
249877803
351145588
If H1 and H2 are different then everythig is good.
Am I missing something?
 
L

~L

The function assumes you have headers. Insert a real header or adjust your
range down one row.
 
M

Mike

This is the real code here
ws.Range(CustomerPONumber & 1 & ":" & CustomerPONumber &
lastrow).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=ws.Range(UniqueRecords & 1), Unique:=True
 
P

Peter T

Untested -

With ws.Range(CustomerPONumber & 1 & ":" & CustomerPONumber & lastrow)
.Rows(1).Insert Shift:=xlDown ' assumes bottom row is empty
.Rows(1) = "dummy"
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range(UniqueRecords & 1), Unique:=True
.Rows(1).Delete Shift:=xlUp
End With
ws.Range(UniqueRecords & 1).Delete Shift:=xlUp

Inserts header(s) in the filter range, filter, delete shift up both top row
of filter-range and copy-to-range

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top