使用高级过滤器获取唯一值不起作用?

问题描述

我有两张纸

表格2:

Column C
Supplier Name
A
A
B
B
C

第1张(所需结果)

Column G
A
B
C

如上所述，我正在尝试在工作表1的G列中创建唯一的供应商名称列表.

I am trying to create a list of unique supplier names in column G on Sheet 1, as shown above.

我正在使用以下代码:

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

此代码无法正常工作.它显示出第一个供应商名称A，如下所示:

This code is not working correctly. It shows the first supplier name A as duplicated like so:

第1张纸

Column G
A
A
B
C

推荐答案

高级过滤器需要在复制到操作中携带的标头行.由于您尚未声明或包含一个，因此r1.AdvancedFilter命令假定C2是标题行.

Advanced Filter requires a header row that it carries across in a Copy To operation. Since you have not assinged or included one, the r1.AdvancedFilter command assumes that C2 is the header row.

将Range("C2:C" & lastrow)更改为Range("C1:C" & lastrow)，以便高级筛选器"具有标题行以供携带.

Change Range("C2:C" & lastrow) to Range("C1:C" & lastrow) so that Advanced Filter has a header row to carry across.

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

请注意，您将把C1传送到Sheet1！G16.如果不需要，请删除它.

Note that you will be carrying C1 across to Sheet1!G16. Delete it if is not desired.

替代直接值传递和RemoveDuplicates代替AdvancedFilter.

Alternate with direct value transfer 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

这篇关于使用高级过滤器获取唯一值不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！