Skip to main content
Commonmark migration
Source Link

Range("A2:A1048576") ' 1048575 cells

 

Range("A2", Range("A" & Rows.Count).End(xlUp)) 'All Cells between A1 and the last non-empty cell in Column A

Range("A2:A1048576") ' 1048575 cells

 

Range("A2", Range("A" & Rows.Count).End(xlUp)) 'All Cells between A1 and the last non-empty cell in Column A

Range("A2:A1048576") ' 1048575 cells

Range("A2", Range("A" & Rows.Count).End(xlUp)) 'All Cells between A1 and the last non-empty cell in Column A

Source Link
user109261
user109261

Using Dynamic Ranges (a Range that resizes to fit the actual data) will give you a huge performance boost.

Range("A2:A1048576") ' 1048575 cells

Range("A2", Range("A" & Rows.Count).End(xlUp)) 'All Cells between A1 and the last non-empty cell in Column A

Reading and writing to an Array is faster then working with the Cells themselves. Use Range.Value or Range.Value2 to return or write an Array of values from or to the target range. Range.Value2 is faster then Range.Value because it ignores formatting.

It is good practice to fully qualify your Ranges. In this way, your code will work as intended no matter what Worksheet or Workbook is active when the procedure is called.

With ThisWorkbook.Worksheets("Sheet1")
    Set listA = .Range("A2:A1048576")

Collections should be used when comparing two or more list. If you need to both a unique identifier (Key) and a reference (Value) then a Scripting.Dictionary is ideal. Scripting.Dictionary have built in methods to lookup values or object references stored in Key/Value pairs. Since the OP just need to lookup Keys, I used an ArrayList in my code.

Refactored Code

Sub Compare_By_Arraylist()
    Dim v As Variant
    Dim listA As Object, listNotA As Object, listMatches As Object
    Set listA = CreateObject("System.Collections.ArrayList")
    Set listNotA = CreateObject("System.Collections.ArrayList")
    Set listMatches = CreateObject("System.Collections.ArrayList")

    With ThisWorkbook.Worksheets("Sheet1")
        For Each v In .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value2
            If Not listA.Contains(v) Then listA.Add v
        Next

        For Each v In .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Value2
            If listA.Contains(v) Then
                If Not listMatches.Contains(v) Then listMatches.Add v
            Else
                listNotA.Add v
            End If
        Next

        For Each v In .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Value2
            If listA.Contains(v) Then listA.Remove v
        Next

        Call UnProtect
        Application.ScreenUpdating = False
        .Range("C1:E1").Value = Array("Files in A that are NOT in B", "Files in B that are Not in A", "Matching Files")
        .Range("C2").Resize(listA.Count).Value = Application.WorksheetFunction.Transpose(listA.ToArray)
        .Range("D2").Resize(listNotA.Count).Value = Application.WorksheetFunction.Transpose(listNotA.ToArray)
        .Range("E2").Resize(listMatches.Count).Value = Application.WorksheetFunction.Transpose(listMatches.ToArray)
        Application.ScreenUpdating = True
        Call Protect

    End With


End Sub