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

问题描述

所以我在做一个宏来做很多事情.一件事是从sheet2中找到sheet1中单元格的重复项.给定工作表1中的columnA，是否使工作表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.

方法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

方法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

如果您提前知道这些值，并且只想将它们列出在变量中，则可以使用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屋！