将整列（每列中的值）放在一个数组中？

So i'm making a macro to do a bunch of things. one thing is find duplicates of cells in sheet1 from sheet2. given columnA in sheet 1, do any values in columnB on sheet2 match any of the values in columna sheet1.

所以我正在制作一个宏来做一堆事情。有一件事是从sheet2中找到sheet1中的单元格重复。给定表1中的columnA，sheet2上columnB中的任何值都与columna sheet1中的任何值匹配。

I know theres a remove duplicates, but I just want to mark them, not remove.

我知道删除重复项，但我只想标记它们，而不是删除。

I was thinking something with the filtering. I know when you filter you can select multiple criteria, so if u have a column with 20 different values in it, you can select 5 values in the filter and it will show rows with those 5 values for the particular column. So i recorded a macro of that, and checked out the code, and I see for that it uses a string array, where each value to search for is in a string array. Is there any way to just specify an entire column and add every value to the string array?

我正在考虑过滤。我知道当你过滤时你可以选择多个标准，所以如果你有一个包含20个不同值的列，你可以在过滤器中选择5个值，它将显示具有特定列的5个值的行。所以我记录了一个宏，并检查了代码，我看到它使用了一个字符串数组，其中每个要搜索的值都在一个字符串数组中。有没有办法只指定整个列并将每个值添加到字符串数组？

thanks in advance

提前致谢

2 个解决方案

#1

Here are three different ways to load items into an array. The first method is much faster but simply stores everything in the column. You have to be careful with this though because it creates a multidimensional array which isn't something that can be passed to AutoFilter.

以下是将项加载到数组中的三种不同方法。第一种方法要快得多，但只是将所有内容存储在列中。你必须要小心这一点，因为它创建了一个多维数组，而不是可以传递给AutoFilter的数组。

Method 1:

方法1：

Sub LoadArray()
    Dim strArray As Variant
    Dim TotalRows As Long

    TotalRows = Rows(Rows.Count).End(xlUp).Row
    strArray = Range(Cells(1, 1), Cells(TotalRows, 1)).Value

    MsgBox "Loaded " & UBound(strArray) & " items!"
End Sub

Method 2:

方法2：

Sub LoadArray2()
    Dim strArray() As String
    Dim TotalRows As Long
    Dim i As Long

    TotalRows = Rows(Rows.Count).End(xlUp).Row
    ReDim strArray(1 To TotalRows)

    For i = 1 To TotalRows
        strArray(i) = Cells(i, 1).Value
    Next

    MsgBox "Loaded " & UBound(strArray) & " items!"
End Sub

if you know the values ahead of time and just want to list them in a variable you can assign a variant using Array()

如果您提前知道值并且只想在变量中列出它们，则可以使用Array（）分配变量

Sub LoadArray3()
    Dim strArray As Variant

    strArray = Array("Value1", "Value2", "Value3", "Value4")

    MsgBox "Loaded " & UBound(strArray) + 1 & " items!"
End Sub

#2

not sure if anyone else will have this problem or not so I figured I'd post the answer I found. I like the solution of the array posted by @Ripster (and thanks for that, it almost worked) but it won't really work in this case. What I'm working with is a large sheet of data with 1 ID column, and I want to check other sheets to see if there are duplicates in that sheet (using ID column). not delete though, just mark so I can check them out. With potentially upwards of 50K rows looping through each row would take a LONG time.

不确定是否有其他人会有这个问题所以我想我会发布我找到的答案。我喜欢@Ripster发布的数组的解决方案（并且感谢它，它几乎可以工作）但在这种情况下它不会真正起作用。我正在使用的是一张包含1个ID列的大量数据，我想检查其他工作表以查看该工作表中是否有重复项（使用ID列）。不删除，只需标记，以便我可以检查出来。循环每行可能超过50K行需要很长时间。

So, what I figured out I can do is copy the ID column from the other sheet into the main sheet, and use the conditional formatting option to mark duplicates in some colour. (It'll mark the rows in both columns) and then I can filter the column by colour to show me only the colour I used to mark the duplicates. If I programmatically add a column to the sheet I'm checking with the row numbers, I can even include that column in the main sheet so when I filter for colour I can see which rows they were in their sheet.

所以，我想通过将其他工作表中的ID列复制到主工作表中，并使用条件格式选项以某种颜色标记重复项。 （它会标记两列中的行）然后我可以按颜色过滤列，只显示我用来标记重复项的颜色。如果我以编程方式向工作表添加一列我正在检查行号，我甚至可以在主工作表中包含该列，因此当我过滤颜色时，我可以看到它们在工作表中的哪些行。

After doing that I can record and adapt a macro to do this automatically for my less programming inclined co-workers

在这样做之后，我可以记录并调整宏来自动执行此操作，以减少编程倾向的同事

Thanks much all!

非常感谢！

---

Edit - Added Code

After selecting the columns to compare, here is the code to mark the duplicates with red text and no fill. -- Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False

选择要比较的列后，这里是用红色文本标记重复项而没有填充的代码。 - Selection.FormatConditions.AddUniqueValues Selection.FormatConditions（Selection.FormatConditions.Count）.SetFirstPriority Selection.FormatConditions（1）.DupeUnique = xlDuplicate With Selection.FormatConditions（1）.Font .Color = -16383844 .TintAndShade = 0 End With Selection .FormatConditions（1）.StopIfTrue = False

and then, since both columns have the duplicates marked you select the one that you actually want to examine and heres the code to filter:

然后，由于两列都标有重复项，因此请选择您要检查的副本，然后使用要过滤的代码：

`Selection.AutoFilter
ActiveSheet.Range("$C$1:$C$12").AutoFilter Field:=1, Criteria1:=RGB(156, 0 _
    , 6), Operator:=xlFilterFontColor`

(in my test i used column c as the one to filter, that can be programmatically with a cells() reference or a range(cells(), cells()) sort of reference

（在我的测试中，我使用列c作为过滤器，可以通过编程方式使用cells（）引用或范围（cells（），cells（））类型的引用

I wish everyone the best of luck in their future endevors! thanks again to @ripster

祝大家在未来的事业中万事如意！再次感谢@ripster