Stutter in AdvancedFilter Unique Results?

[Ed]

After pasting a data range into my template workbook (Paste Special - Values
Only), I filter two ranges for Unique Values using
' Filter unique values
' Subsystems
Range("SUBSYSTEM").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AA1"), Unique:=True
' Chargeability
Range("CHARGEABILITY").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range( _
"AD1"), Unique:=True

It's not unusual to get a "stutter" - one value showing up twice. The full
range has 862 values; the filter yields 12 Unique Values, which is the same
as what AutoFilter shows. But one value is repeated. The AutoFilter
doesn't show it twice. Neither result from the filter shows an extra space,
although I haven't checked the 239 out of the 862 that match the repeated
value.

Any suggestions?

Ed

 

[Tom Ogilvy]

This is generally caused because you don't have a header row. the
Autofilter assumes your data range is like

Header1 Header2
Data Data
Data Date

If you have

Data Data
Data Data
Data Date

it treats the first row as a header and copies it - thus if that value is
found later in the table, you get a single duplicate entry.

May not be your problem, but it is the most likely that occurs to me.

 

[Ed]

The data ranges are set in the template, and the data is pasted into the
ranges. There are headers on the template for the data ranges in Row 1, but
the range definitions start at Row 2. When I had the ranges include Row 1,
I got the headers as part of my Unique Values list, so I changed it.

Would it be better to redefine the ranges back to Row 1 and Find/Delete my
header value if it shows up? Or am I missing a parameter in my filter?

Ed

 

[Tom Ogilvy]

Your choice Ed, delete the headers or start in row 2 and delete the
duplicate - but if that is the only place that particular value occurs, you
will loose it. So I would start in row1 and delete the headers if you don't
want them.

 

[Ed]

Thanks for the help, Tom.

Ed

Your choice Ed, delete the headers or start in row 2 and delete the
duplicate - but if that is the only place that particular value occurs, you
will loose it. So I would start in row1 and delete the headers if you don't
want them.