FindUniqueValues Issue

[GonzoB]

Hi Helpers,

I have an issue with function FindUniqueValues. When I'm running it in my target field I can always find one value appearing twice. This is 17583 (row 2 and 4). Can somebody help to understand why this is happening and how to resolve?

Source table:

ZTT Roster-vlookup-data.xlsm
	A	B	C	D	E
1	column A	column B	column C	column D	column E
2	xyz	17583	xyz	xyz	xyz
3	xyz	17839	xyz	xyz	xyz
4	xyz	17583	xyz	xyz	xyz
5	xyz	17583	xyz	xyz	xyz
6	xyz	17583	xyz	xyz	xyz
7	xyz	17839	xyz	xyz	xyz
8	xyz	17839	xyz	xyz	xyz
9	xyz	17839	xyz	xyz	xyz
10	xyz	18924	xyz	xyz	xyz
11	xyz	19080	xyz	xyz	xyz
12	xyz	19088	xyz	xyz	xyz
13	xyz	19089	xyz	xyz	xyz
14	xyz	19542	xyz	xyz	xyz
15	xyz	17611	xyz	xyz	xyz
Export

Target Sheet:

ZTT Roster-vlookup-data.xlsm
	A	B	C	D
1		column B
2		17583
3		17839
4		17583
5		18924
6		19080
7		19088
8		19089
9		19542
10		17611
11		17622
12		17699
13		17312
14		17313
15		17314
16		17317
ZTT

VBA:

Sub create_Results_sheet()


Dim ws1 As Worksheet
Dim sheet_name As String

sheet_name = "Results"

With ThisWorkbook
    Set ws1 = .Sheets.Add(After:=.Sheets(.Sheets.Count))
    ws1.Name = sheet_name
    
    Cells(1, 2).Value2 = "column B"

End With


Dim ExportRow As Long
ExportRow = Worksheets("Export").Cells(Rows.Count, 1).End(xlUp).Row


FindUniqueValues Worksheets("Export").Range("B2:B" & ExportRow), Worksheets(sheet_name).Range("B2")


End Sub

Sub FindUniqueValues(SourceRange As Range, TargetCell As Range)

SourceRange.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetCell, Unique:=True
        
End Sub

 

[Fluff]

Try

FindUniqueValues Worksheets("Export").Range("B1:B" & ExportRow), Worksheets(sheet_name).Range("B2")

Otherwise B2 is being treated as a header, rather than a value.

 

[GonzoB]

Thanks Fluff.

What I also realized is that there's no way to exclude the header from the listing of the distinct values. Therefore I'm using now a Delete Row command at the end of the script to remove the header value from my target sheet.

 

[Fluff]

Glad to help & thanks for the feedback.