excel - 为什么 RemoveDuplicates 代码会使顶部单元格重复？

Question

我正在使用 .RemoveDuplicates 代码删除重复值，但它不会将顶行值作为重复项删除。例如，如果 John Smith 是最高值，并且 John Smith 也出现在列表的另一行中，则最终结果将有 John Smith 两次。数据每天都在变化，但无论我尝试什么，第一行的值仍然是重复的。

有人可以告诉我我做错了什么吗？

LastRow = Sheets("Pipeline").Cells(Sheets("Pipeline").Rows.Count, "B").End(xlUp).Row
Sheets("Pipeline").Range("B11:B" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets(2).Range("D2"), Unique:=True
Range("A10").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range("A11").Select

LastRow2 = Sheets(2).Cells(Sheets(2).Rows.Count, "D").End(xlUp).Row
Sheets(2).Range("D2:D" & LastRow2).Copy
Sheets("Validation Data").Range("A2").PasteSpecial Paste:=xlPasteValues
Sheets("Validation Data").Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
Application.CutCopyMode = False

标签： excelvba

Answer 1

您的问题是高级过滤器副本的已知问题。您无需执行两个不同的操作即可删除重复项。只需将“管道”表中的范围复制到“验证数据”表中，然后使用RemoveDuplicates. 虽然我更喜欢“等于方法”而不是复制/粘贴。代码中提供了注释。如果您有任何问题，请询问。

'Always define and set your variables; source worksheet, last row, copy range, copy range row count, and destination worksheet

Dim srcws As Worksheet: Set srcws = ThisWorkbook.Sheets("Pipeline")
Dim LastRow As Long: LastRow = srcws.Cells(srcws.Rows.Count, "B").End(xlUp).Row
Dim cpyRng As Range: Set cpyRng = srcws.Range("B2:B" & LastRow)
Dim cnt As Long: cnt = cpyRng.Rows.Count

Dim destws As Worksheet: Set destws = ThisWorkbook.Sheets("Validation Data")

'Use "equals method" to transfer a range from one sheet to another
'destws is the sheet you will be moving the data to from the srcws
'Resize the destination range with "cnt" to ensure it is equal to the source range

    With destws
        .Range("A2").Resize(cnt).Value = cpyRng.Value
        .Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
    End With