Get unique values with advanced filters not working?
I have two sheets:
Sheet 2:
Column C
Supplier Name
A
A
B
B
C
Sheet 1 (desired result)
Column G
A
B
C
I am trying to create a list of unique vendor names in column G on sheet 1 as shown above.
I am using this code:
Sub LIST()
Dim r1 As Range, r2 As Range
Dim lastrow As Long
lastrow = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).row
Set r1 = Sheets("Data").Range("C2:C" & lastrow)
Set r2 = Sheets("Sheet1").Range("G16")
r1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=r2, unique:=True
End Sub
This code does not work correctly. It shows the name of the first provider A as duplicate:
Sheet 1
Column G
A
A
B
C
source to share
An advanced filter requires a header string, which it executes in a copy operation. Since you haven't rated or included it, the command r1.AdvancedFilter
assumes C2 is the title bar.
Change Range("C2:C" & lastrow)
to Range("C1:C" & lastrow)
so that the advanced filter has a header line to wrap.
Sub LIST()
Dim r1 As Range, r2 As Range
Dim lastrow As Long
lastrow = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).Row
Set r1 = Sheets("Data").Range("C1:C" & lastrow)
Set r2 = Sheets("Sheet1").Range("G16")
r1.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=r2, Unique:=True
End Sub
Note that you are transferring C1 through Sheet1! G16. Delete it if you don't want it.
Alternative with direct value passing and RemoveDuplicates instead of AdvancedFilter.
Sub nodupeLIST()
Dim r1 As Range, lastrow As Long
With Worksheets("Data")
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
Set r1 = .Range("C2:C" & lastrow)
End With
With Worksheets("Sheet1")
With .Range("G16").Resize(r1.Rows.Count, 1)
.Cells = r1.Value
.RemoveDuplicates Columns:=1, Header:=xlNo
End With
End With
End Sub
source to share