vba 将整列（列中的每个值）放入数组中？

Put entire column (each value in column) in an array?

提问by user1759942

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 中的列 A，使工作表 2 上列 B 中的任何值与列 A 中工作表 1 中的任何值匹配。

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

提前致谢

回答by Ripster

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:

方法一：

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:

方法二：

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

回答by user1759942

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:$C").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()引用或range(cells(), cells())某种引用

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

我祝大家在他们未来的努力中好运！再次感谢@ripster