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

Question

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.

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?

thanks in advance

Answer 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.

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()

Sub LoadArray3()
    Dim strArray As Variant

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

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