hi, below is my code to compare two sheets , but i need this get updated so that i want to compare one perticular col (col D) from both sheets , and what ever data matches it should be marked as present in col M or else removed in one worksheet
and next step , for all the present (col m) values , it as to compare col i from both sheets, if data match then do nothing else replace those rows from other worksheet to this one worksheet.
Sub compare()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim iRo As Long
Dim iCo As Long
strRangeToCheck = "A1:IV25536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet2").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet3").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)
For iRo = LBound(varSheetB, 1) To UBound(varSheetB, 1)
For iCo = LBound(varSheetB, 2) To UBound(varSheetB, 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.
MsgBox "Macro completed successfully @" & Now
End If
Next iCo
Next iRo
Next iCol
Next iRow
End Sub
and next step , for all the present (col m) values , it as to compare col i from both sheets, if data match then do nothing else replace those rows from other worksheet to this one worksheet.
Sub compare()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
Dim iRo As Long
Dim iCo As Long
strRangeToCheck = "A1:IV25536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet2").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet3").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)
For iRo = LBound(varSheetB, 1) To UBound(varSheetB, 1)
For iCo = LBound(varSheetB, 2) To UBound(varSheetB, 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.
MsgBox "Macro completed successfully @" & Now
End If
Next iCo
Next iRo
Next iCol
Next iRow
End Sub