ashupuneet
02-04-2017, 09:43 PM
HI
I need a help in preparing a macro which identify the duplicate in column C of sheet1 followed by removing old row and retain new row but before deletion of old row i wish to highlight the cell in color red of new row (which is retained after duplicate remove) where values has changed between duplicate old row and new row.
if possible also that changed value insert as comment in new row cell in same column.
Now i am using this macro to identify the duplicate and remove old entries
Sub Delete_Dups_Keep_Last()
Dim i As Long
Dim j As Long
Worksheets("Sheet1").Activate
Dim ROW_DELETED As Boolean
i = 2 'start on second row
Application.ScreenUpdating = False
Do While i <= Sheets("Sheet1").UsedRange.Rows.Count
ROW_DELETED = False
For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 3) = Cells(j, 3) Then
Rows(i).Delete
ROW_DELETED = True
Exit For
End If
Next j
If Not ROW_DELETED Then i = i + 1
Loop
Application.ScreenUpdating = True
End Sub
In sheet 1 row 3 and row 9 having duplicate value in column C and wish to highlight and insert changed value as a comment in row 9.
Confirmed delivery date
STP
MO
Long Description
Prod. Line
Prod
Area Crop
Order Type
Supplier
Finish date (DMY)
Kg's
Packages / TH's
TH's
1
1337972
WMW ***
OK
CCP
XP1
2/7/2017
262.761
2628.661
1
1337975
WMW ***
OK
CCP
XP1
2/7/2017
285.72
2843.77
1
1337976
WMW ***
OK
CCP
XP1
2/7/2017
260.381
2615.787
1
1337978
WMW ***
OK
CCP
XP1
2/9/2017
277.352
2855.618
1
1337980
WMW ***
OK
CCP
XP1
2/9/2017
240.667
2439.644
1
1337981
WMW ***
OK
CCP
XP1
2/9/2017
280.345
3005.02
1
1337982
WMW ***
OK
CCP
XP1
2/10/2017
276.225
2894.56
1
1337975
WMW ***
OK
CCP
XP1
2/10/2017
247.926
2861.062
Thanks in Advance
I need a help in preparing a macro which identify the duplicate in column C of sheet1 followed by removing old row and retain new row but before deletion of old row i wish to highlight the cell in color red of new row (which is retained after duplicate remove) where values has changed between duplicate old row and new row.
if possible also that changed value insert as comment in new row cell in same column.
Now i am using this macro to identify the duplicate and remove old entries
Sub Delete_Dups_Keep_Last()
Dim i As Long
Dim j As Long
Worksheets("Sheet1").Activate
Dim ROW_DELETED As Boolean
i = 2 'start on second row
Application.ScreenUpdating = False
Do While i <= Sheets("Sheet1").UsedRange.Rows.Count
ROW_DELETED = False
For j = i + 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 3) = Cells(j, 3) Then
Rows(i).Delete
ROW_DELETED = True
Exit For
End If
Next j
If Not ROW_DELETED Then i = i + 1
Loop
Application.ScreenUpdating = True
End Sub
In sheet 1 row 3 and row 9 having duplicate value in column C and wish to highlight and insert changed value as a comment in row 9.
Confirmed delivery date
STP
MO
Long Description
Prod. Line
Prod
Area Crop
Order Type
Supplier
Finish date (DMY)
Kg's
Packages / TH's
TH's
1
1337972
WMW ***
OK
CCP
XP1
2/7/2017
262.761
2628.661
1
1337975
WMW ***
OK
CCP
XP1
2/7/2017
285.72
2843.77
1
1337976
WMW ***
OK
CCP
XP1
2/7/2017
260.381
2615.787
1
1337978
WMW ***
OK
CCP
XP1
2/9/2017
277.352
2855.618
1
1337980
WMW ***
OK
CCP
XP1
2/9/2017
240.667
2439.644
1
1337981
WMW ***
OK
CCP
XP1
2/9/2017
280.345
3005.02
1
1337982
WMW ***
OK
CCP
XP1
2/10/2017
276.225
2894.56
1
1337975
WMW ***
OK
CCP
XP1
2/10/2017
247.926
2861.062
Thanks in Advance