Compare 2 Excel data sheets

Good Day

I am new to VB and are starting to use VB with excel.

I have two different data sheets and want to compare the 2 and wants VB to give the results were the data in one is not in the other and vice versa.

As soon as have that done, I want to use this VB code on other 2 sets of data, but the ranges will differ.

So each time the ranges must be update automatic.

Example: Sheet1 contains data from company 1
Sheet2 contains data from company 2.

It is intercompanies and now if want to make sure that all transaction between the intercompanies are recored and if not, a report in a new sheet must give the transaction that are not recored and in which company.

I hope that will be enough information. let me know if you need more.

Are both sheets in the same workbook?

What you want to do is to identify the "UsedRange" for each sheet and make shure they are equal. Then you want to iterate through all of the cells in one sheet and compare the value in each cell to the value in the corresponding cell on the other sheet. Here is some demo code to get you going.

Code:

---

Option Explicit

Sub Compare_Sheets()
    Dim aCell As Range
   
    'Do a sanity check to make sure the sheets use the same areas
    If Sheets("Sheet1").UsedRange.Address <> Sheets("Sheet2").UsedRange.Address Then
        MsgBox "Sheets have different ranges of occupied cells"
        End
    End If

    'Iterate through 1 sheet comparing each cell with the other sheet
    For Each aCell In Sheets("Sheet1").UsedRange.Cells
        If aCell.Value <> Sheets("Sheet2").Range(aCell.Address).Value Then
            MsgBox "Sheets do NOT match at cell " & aCell.Address
        End If
    Next aCell

End Sub

---

If "UsedRange" gives you strange results, do a search for "ActualUsedRange" on this forum.