Compare Two Worksheets with VBA
Compare two worksheets with VBA and output the differences to a new worksheet. With the following vba macro, you can compare the contents of two worksheets and only display the differences. The following will compare Column A in the active sheet (Sheet1) for example with Column A in Sheet2. It uses the scripting dictionary so the process is very fast for high volumes of data. Of course this procedure could be replicated with a Vlookup in native Excel without the output to a new area.
Sub NoMatches() 'Excel vba to remove duplicates.
Dim dic As Object
Dim ar As Variant
Dim ar1 As Variant
Dim var As Variant
Dim i As Long
Dim n As Long
Set dic=Createobject("Scripting.Dictionary")
dic.CompareMode=1
ar=Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
var=Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp)).Value
ReDim ar1(1 To UBound(var), 1 To 1)
'Loop through ar and add to Dictionary.
ar1(n, 1)=var(i, 1)
Sheet3.Range("E2:E" & UBound(var)).Value=ar1
Range("E2:E" & UBound(var)).RemoveDuplicates 1
End Sub
In the following file I have included 2 procedures. One compares two lists with vba and outputs items which are the same. This article is similar to the article on comparing two lists with VBA. The other compares two lists with vba and outputs the differences.
The following will compare the contents of each cell in each line in one sheet (multiple columns) with the contents of each line in another worksheet. This takes the concept from above one step further where you are comparing the contents of 2, 3, 10 etc items in a row. The result is displayed in a new workbook listing all cell differences.
If you only want to compare one column (similar to the compare procedure above) of data you can adapt the following by putting the data in Column A of both sheets. Ensure you leave one column blank (col B). Or change the following lines;
ar=Sheet1.Cells(10, 1).CurrentRegion.Value
ar=Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value
So that the ranges cover just one column rather than the current region.
Sub CompareIt() 'Excel VBA with scripting dictionary to compare sheets
Dim ar As Variant
Dim arr As Variant
Dim Var As Variant
Dim v()
Dim i As Long
Dim n As Long
Dim j As Long
Dim str As String
ar = Sheet1.Cells(10, 1).CurrentRegion.Value
.CompareMode = 1
ReDim v(1 To UBound(ar, 2))
v(n) = ar(i, n)
ar = Sheet2.Cells(10, 1).CurrentRegion.Resize(, UBound(v)).Value
For i = 2 To UBound(ar, 1)
v(n) = ar(i, n)
If .exists(str) Then
str = ""
For Each arr In .keys
Var = .items: j = .Count
With Sheet3.Range("a1").Resize(, UBound(ar, 2))
.CurrentRegion.ClearContents
If j > 0 Then
The Excel file attached shows workings with the cells in yellow showing the differences.