Compare all cells of two Excel files or sheets

@Jean-François Corbett – Load an entire sheet at once into a Variant array. In Excel 2003, this takes about 2 seconds (and 250 MB of RAM). Then you can loop through it in no time at all.

'src -
Option Explicit

Sub test()

    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long

    strRangeToCheck = "A1:IV65536"
    ' If you know the data will only be in a smaller range, reduce the size of the ranges above.
    Debug.Print Now
    varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
    varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
    Debug.Print Now

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
            If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
                ' Cells are identical.
                ' Do nothing.
                ' MsgBox (iRow & "," & iCol)
                ' Debug.Print (iRow & "," & iCol)
                ' Cells are different.
                ' Code goes here for whatever it is you want to do.
            End If
        Next iCol
    Next iRow

End Sub

'To compare to a sheet in a different workbook, open that workbook and get the sheet as follows:

'Set wbkA = Workbooks.Open(filename:="C:\MyBook.xls")
'Set varSheetA = wbkA.Worksheets("Sheet1") ' or whatever sheet you need 

#vba #compare

By: |18/04/2018|categories: