如何从 Excel VBA 中的列中获取唯一值列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20736084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get a list of unique values from a column in Excel VBA?
提问by Nilay Panchal
I've gone through many different articles that filter unique values; however almost all of them copy to a new location / loop through the entire column manually which I would like to avoid doing.
我浏览了许多过滤唯一值的不同文章；然而，几乎所有这些都复制到一个新位置/手动循环遍历整个列，我想避免这样做。
My requirement is to separate the rows from a single column in separate workbooks, based on the value of G2 column's cell (eg. All Houses, All Condos, All Apartments etc in separate files). I am attempting to use the AdvancedFilter in Excel VBA to extract each unique value into an array and then iterate through these unique values finding all the corresponding column values. I don't get how the AdvancedFilter works though.
我的要求是根据 G2 列单元格的值（例如，单独文件中的所有房屋、所有公寓、所有公寓等）将行与单独工作簿中的单个列分开。我试图在 Excel VBA 中使用 AdvancedFilter 将每个唯一值提取到一个数组中，然后遍历这些唯一值以找到所有相应的列值。我不明白 AdvancedFilter 是如何工作的。
xlFilterCopy along with copyToRange copies the list of unique values to the specified range.
xlFilterCopy 和 copyToRange 将唯一值列表复制到指定范围。
How in the world does xlFilterValues work though? The reference says xlFilterValues "filters the data in place". But how do I GET these values? Does it return an object of some kind? In debug it just seems to give me a boolean value!
xlFilterValues 到底是如何工作的？参考文献说 xlFilterValues “就地过滤数据”。但是我如何获得这些值呢？它是否返回某种对象？在调试中，它似乎给了我一个布尔值！
Why is this so oddly documented! Thanks for the help people.
为什么这个记录如此奇怪！感谢人们的帮助。
PS: If you have a better way to do what I need without the advancedfilter please let me know.
PS：如果您有更好的方法可以在没有高级过滤器的情况下完成我需要的操作，请告诉我。
With sheet
.Range(orgLevelLetter & "2", .Cells(.Rows.Count, orgLevelLetter).End(xlUp)).AdvancedFilter xlFilterInPlace, , , True
End With
回答by Lance Roberts
You get the values because you specified the CopyToRange, therefore you know where the values went. So whatever you just used as a reference for that, you now use to manipulate it.
您获得值是因为您指定了CopyToRange，因此您知道值的去向。因此，无论您刚刚用作参考的任何内容，您现在都可以用来操作它。
If you don't want to use the CopyToRange, then the values are returned by the call because AdvancedFilterreturns a range object. Therefore you probably want to assign your .Range statement to a range that you have initialized already.
如果您不想使用CopyToRange，则调用会返回值，因为AdvancedFilter返回一个范围对象。因此，您可能希望将 .Range 语句分配给您已经初始化的范围。
An alternative way of doing that would be to use the With statement this way:
另一种方法是这样使用 With 语句：
With sheet.Range(orgLevelLetter & "2", _
.Cells(.Rows.Count,orgLevelLetter).End(xlUp)).AdvancedFilter(xlFilterInPlace, , , True)
<content here>
End With
