VBA Code to Compare Two Worksheets with unequal no of Rows

[mishrav98]

Hi All,
I'm new in VBA and need your help in developing a Macro which can compare the 2 worksheets with different number of rows and highlight the differences in a seperate workbook.

I have tried below code for this but the problem with the code is that it compares each cell in sheet1 to each cell in sheet2 and not vice versa. that works perfectly if both the sheets have same no of rows. but if they have different no of rows in either of the sheets,then it fails.

Sub CommandButton3_Click()

Dim varSheetA As Variant
Dim varSheetB As Variant
Dim varSheetC As Variant
Dim varSheetD As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim iRowA As Long
Dim iColA As Long
Dim LovClm As String
Dim LovVal1 As String
Dim LovVal2 As String

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
lr3 = 2

' 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("A2:K1400")
varSheetB = Worksheets("Sheet2").Range("A2:K1400")
'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
'Do nothing
Else
ThisWorkbook.Sheets("Sheet2").Cells(iRow + 1, iCol).Interior.Color = vbRed
ThisWorkbook.Sheets("Sheet1").Cells(iRow + 1, iCol).Interior.Color = vbRed
ThisWorkbook.Sheets("Sheet2").Activate
LovClm = Sheets("Sheet2").Cells(1, iCol).Value
LovVal1 = Sheets("Sheet2").Cells(iRow + 1, iCol).Value
ThisWorkbook.Sheets("Sheet1").Activate
LovVal2 = Sheets("Sheet1").Cells(iRow + 1, iCol).Value
'Sheets("Sheet2").Range(Sheets("Sheet2").Cells(iRow + 1, 1), Sheets("Sheet2").Cells(iRow + 1, maxC)).Select
'Selection.Copy
Worksheets("Report").Activate
Worksheets("Report").Select
Worksheets("Report").Range(Worksheets("Report").Cells(lr3, 1), Worksheets("Report").Cells(lr3, maxC)).Select
Worksheets("Report").Cells(lr3, 1).Value = LovClm
Worksheets("Report").Cells(lr3, 2).Value = LovVal1
Worksheets("Report").Cells(lr3, 3).Value = LovVal2
'Selection.PasteSpecial
Application.CutCopyMode = False
lr3 = lr3 + 1

End If
Next iCol
Next iRow



End Sub

 

[hiker95]

mishrav98,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


If posting VBA code, please use Code Tags - like this:

[code]

Paste your code here.

[/code]


Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.