创建一个没有重复的新列VBA？ [英] Make a new column without duplicates VBA?

问题描述

我有一列单元格，其值如下所示：

I have a column of cells whose values are something like this:

a
a
b
b
c
c
c
c
d
e
f
f

等。

我正在寻找不重复的值并将其粘贴到一个新的列中。我的伪代码如下：

I'm looking to take the non-duplicated values and paste them into a new column. My pseudocode for this is as follows:

ActiveSheet.Range("a1").End(xlDown).Select
aend = Selection.Row
for acol= 1 to aend
    ActiveSheet.Range("b1").End(xlDown).Select
    bend = Selection.Row
        'if Cells(1,acol).Value <> any of the values in the range Cells(2,1).Value
        'to Cells(2,bend).Value, then add the value of Cells(1,acol) to the end of 
        'column b.

我的逻辑是否有意义？我不知道如何编写已注释的部分。如果这不是最有效的方法，有人可以建议一个更好的方法吗？非常感谢！

Does my logic in this make sense? I'm not sure how to code the commented portion. If this isn't the most efficient way to do it, could someone suggest a better way? Thanks so much!

推荐答案

根据您使用的Excel版本，您可以使用一些内置的Excel功能来获取您想要什么 - 整个解决方案取决于您使用VBA的技能水平。

Depending on which version of Excel you are using, you can use some built-in Excel functionality to obtain what you want- the whole solution depends on your level of skill with VBA.

Excel 2003 ：

您可以使用 Advancedfilter 方法（文档），以获取唯一值并将其复制到目标区域。示例：

You can use the Advancedfilter method (documentation) of your range to obtain the unique values and copy them to your target area. Example:

With ActiveSheet
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("B1"), Unique:=True
End With

其中 B1 是要将唯一值复制到列的第一个单元格。此方法的唯一问题是即使重复，源列（A1）的第一行将被复制到目标范围。这是因为AdvancedFilter方法假定第一行是标题。

Where B1 is the first cell of the column you wish to copy the unique values to. The only problem with this method is that the first row of the source column ("A1") will be copied to the target range even if it is duplicated. This is because the AdvancedFilter method assumes that the first row is a header.

因此，添加一条额外的代码行：

Therefore, adding an additional code line we have:

With ActiveSheet    
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("B1"), Unique:=True
    .Range("B1").Delete Shift:=xlShiftUp
End With

Excel 2007/2010 ：

您可以使用与上述相同的方法，或使用 RemoveDuplicates 方法（文档）。这与AdvancedFilter方法类似，除了 RemoveDuplicates 在原地工作，这意味着您需要复制源列，然后执行过滤，例如：

You can use the same method as above, or use the RemoveDuplicates method (documentation). This is similar to the AdvancedFilter method, except that RemoveDuplicates works in-place, which means you need to make a duplicate of your source column and then perform the filtering, for example:

With ActiveSheet
    .Range("A1", .Range("A1").End(xlDown)).Copy Destination:=.Range("B1")
    .Range("B1", .Range("B1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
End With

最终参数标题控件源数据的第一个单元格是否被复制到目的地（如果它设置为true，那么类似于AdvancedFilter方法的方法）。

The final parameter Header controls whether the first cell of the source data is copied to the destination (if it's set to true then the method similarly to the AdvancedFilter method).

如果你在purer方法，那么你可以使用VBA Collection 或字典 - 我相信别人会提供解决方案。

If you're after a "purer" method, then you can use a VBA Collection or dictionary - I am sure that someone else will offer a solution with this.

这篇关于创建一个没有重复的新列VBA？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！