FilterCopy Unique Values Visible Only

[STIguy]

Experts,

I would like to be able to filter a list and copy the unique values from a range in another column for the visible cells only. Some digging has provided some examples using xlFilterCopy but I can't seem to get it to work for the visible cells. See the attached data set below.

[TABLE="width: 315"]
<tbody>[TR]
[TD]L1 No.[/TD]
[TD]L2 Item[/TD]
[TD]L2 No.[/TD]
[TD]L3 No.[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]501[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]502[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]503[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]504[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]506[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]601[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]602[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]501[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]502[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]505[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]601[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]602[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]603[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]604[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]501[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]502[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]1[/TD]
[TD]101[/TD]
[TD]505[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]601[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]602[/TD]
[/TR]
[TR]
[TD]3000[/TD]
[TD]2[/TD]
[TD]102[/TD]
[TD]603[/TD]
[/TR]
</tbody>[/TABLE]

In this example I am trying to find the unique L3 No values for each L2 item. If we filter for a value of 1 under L2 item and find the unique values for L3, we are left with the following list:

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]501[/TD]
[/TR]
[TR]
[TD="align: right"]502[/TD]
[/TR]
[TR]
[TD="align: right"]503[/TD]
[/TR]
[TR]
[TD="align: right"]504[/TD]
[/TR]
[TR]
[TD="align: right"]505[/TD]
[/TR]
[TR]
[TD="align: right"]506[/TD]
[/TR]
</tbody>[/TABLE]

I would like to be able to copy and paste this list to another worksheet. I am not very familiar with using advanced filters in VBA, but from what I have observed they seem to be very efficient. I did write some similar code to highlight the unique values in a list but it doesnt extract those values. That code is attached below for reference. Thanks!

Sub HighlightUnique()

    Dim ActSheet As Worksheet
    Dim SelRange As Range


    Set ActSheet = ActiveSheet
    Set SelRange = Selection
    Selection.EntireColumn.FormatConditions.Delete
    With Selection.SpecialCells(xlCellTypeVisible).Select
    
        Selection.FormatConditions.AddUniqueValues
        Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
        Selection.FormatConditions(1).DupeUnique = xlUnique


        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 10284031
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        
    End With
    
End Sub

 

[GerryZ]

@STYguy
And if you use a formula like this will be the some?

Excel Workbook
	A	B	C	D	E
1	No.	L2 Item	L2 No.	L3 No.	unique L3
2	1000	1	101	501	501
3	1000	1	101	502	502
4	1000	1	101	503	503
5	1000	1	101	504	504
6	1000	1	101	506	506
7	1000	2	102	601	601
8	1000	2	102	602	602
9	2000	1	101	501	505
10	2000	1	101	502	603
11	2000	1	101	505	604
12	2000	2	102	601
13	2000	2	102	602
14	2000	2	102	603
15	2000	2	102	604
16	3000	1	101	501
17	3000	1	101	502
18	3000	1	101	505
19	3000	2	102	601
20	3000	2	102	501
21	3000	2	102	603
Sheet

let me know!

 

[STIguy]

GerryZ,

Much appreciated! However, i'm trying to add this function to an already existing Sub in VBA so I would like to avoid using embedded spreadsheet formulas.

Is it possible to remove duplicates from a selection (and/or highlight) without removing the original data?

Alex

 

[GerryZ]

@STYguy
sorry I forgot the condition
now looks better
let me know if the formula is better!
ciao from italy

Excel Workbook
	A	B	C	D	E
1	No.	L2 Item	L2 No.	L3 No.	unique L3
2	1000	1	101	501	501
3	1000	1	101	502	601
4	1000	1	101	503	602
5	1000	1	101	504	603
6	1000	1	101	506	604
7	1000	2	102	601
8	1000	2	102	602
9	2000	1	101	501
10	2000	1	101	502
11	2000	1	101	505
12	2000	2	102	601
13	2000	2	102	602
14	2000	2	102	603
15	2000	2	102	604
16	3000	1	101	501
17	3000	1	101	502
18	3000	1	101	505
19	3000	2	102	601
20	3000	2	102	501
21	3000	2	102	603
Sheet