将整列(列中的每个值)放入数组中? [英] Put entire column (each value in column) in an array?

问题描述

所以我正在制作一个宏来做很多事情.一件事是从 sheet2 中找到 sheet1 中的单元格的重复项.给定工作表 1 中的列 A，工作表 2 上的列 B 中的任何值是否与工作表 1 中的任何值相匹配.

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.

我知道有一个删除重复项，但我只想标记它们，而不是删除.

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

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

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?

提前致谢

推荐答案

这里有三种不同的方式将项目加载到数组中.第一种方法要快得多，但只是将所有内容存储在列中.不过，您必须小心这一点，因为它会创建一个多维数组，而该数组不能传递给 AutoFilter.

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.

方法一:

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

方法二:

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

如果您提前知道这些值并且只想将它们列在一个变量中，您可以使用 Array() 分配一个变量

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

Sub LoadArray3()
    Dim strArray As Variant

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

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

这篇关于将整列(列中的每个值)放入数组中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！