VBA Macro to compare all cells of two Excel files

Question

I m trying to compare two Excel files and store what s only there in the new file in one sheet and store what is only there in the old one in another sheet   Basically new - old   sheet1 and old - new   sheet2   One SO answers suggest to loop through all cells and do a simple comparison  I m  very  new to VBA and Macros so I don t know how to do this  How is it done  or where can I learn this

User · Answer

A very simple check you can do with Cell formulas:

Sheet 1 (new - old)

=(if(AND(Ref_New<>"";Ref_Old="");Ref_New;"")

Sheet 2 (old - new)

=(if(AND(Ref_Old<>"";Ref_New="");Ref_Old;"")

This formulas should work for an ENGLISH Excel. For other languages they need to be translated. (For German i can assist)

You need to open all three Excel Documents, then copy the first formula into A1 of your sheet 1 and the second into A1 of sheet 2. Now click in A1 of the first cell and mark "Ref_New", now you can select your reference, go to the new file and click in the A1, go back to sheet1 and do the same for "Ref_Old" with the old file. Replace also the other "Ref_New".

Doe the same for Sheet two.

Now copy the formaula form A1 over the complete range where zour data is in the old and the new file.

But two cases are not covered here:

In the compared cell of New and Old is the same data (Resulting Cell will be empty)
In the compared cell of New and Old is diffe data (Resulting Cell will be empty)

To cover this two cases also, you should create your own function, means learn VBA. A very useful Excel page is cpearson.com

User · Answer

Do NOT loop through all cells   There is a lot of overhead in communications between worksheets and VBA  for both reading and writing  Looping through all cells will be agonizingly slow  I m talking hours    Instead  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    In Excel 2007 and later  sheets are about 1000 times larger  1048576 rows    16384 columns   17 billion cells  compared to 65536 rows    256 columns   17 million in Excel 2003   You will run into an  Out of memory  error if you try to load the whole sheet into a Variant  on my machine I can only load 32 million cells at once  So you have to limit yourself to the range you know has actual data in it  or load the sheet bit by bit  e g  30 columns at a time    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              Else                   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] VBA Macro to compare all cells of two Excel files

Examples related to vba

Examples related to excel