从列中删除重复项，但在另一个工作表上使列具有VBA的结果 [英] Removing Duplicates from a Column but make columns on another sheet the outcome with VBA

问题描述

所以我试图从列中删除重复的ID，并将结果粘贴到另一张表上。我目前的解决方法是将整个列复制到新的工作表，然后删除那里的重复项，但现在是非常税务的，因为有60k行，我现在要为多个列执行此操作。

So I am trying to remove duplicate IDs from a column and paste the outcome onto another sheet. My current workaround is to just copy the entire column to the new sheet first and remove the duplicates there but that is very taxing right now as there are 60k rows and I want to now do this for multiple columns.

问题：有没有更好的方法来做到这一点，所以我不必首先复制列。

Question: Is there a better way to do this so I dont have to copy the column over first.

这是我现在的代码。

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Set wb = Workbooks("Test.xlsx")
Set ws = wb.Worksheets(1)
Set ws2 = wb.Worksheets(4)

ws.Range("A1:A" & rowz) = ws2.Range("A1:A" & rowz)

with ws2
Set CtrlID = ws2.Range("A1:A" & rowz)
CtrlID.RemoveDuplicates Columns:=1, Header:=xlYes
end with

推荐答案

如果您在实现Dictionary方法时遇到问题，可以尝试： p>

If you're having trouble implementing the Dictionary approach, you could try this:

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Dim dict as Object
Dim r as Range
Set wb = Workbooks("Test.xlsx")
Set ws = wb.Worksheets(1)
Set ws2 = wb.Worksheets(4)

Set dict = CreateObject("Scripting.Dictionary")

'Assign each value to the dictionary
' overwrites existing values and ensures no duplicates
For each r in ws.Range("A1:A" & rowz).Cells
    dict(r.Value) = r.Value
Next

'## Put the dictionary in to the other worksheet:
ws2.Range("A1").Resize(Ubound(dict.Keys) + 1, 1).Value = Application.Transpose(dict.Keys)

Set dict = Nothing

不过我真的不明白重新发明轮子的原因。您可以运行一些测试来查看哪个更快。

HOWEVER I don't really see a reason to re-invent the wheel. You could run some tests to see which is faster.

这篇关于从列中删除重复项，但在另一个工作表上使列具有VBA的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！