How to remove duplicates and replace with blank cells in Excel

[gopinath1990]

Hi All,

Please help regarding excel doubts since this is my first question to the forum and im a newbie,

1. How to remove duplicates and replace the rows the with blanks where other duplicates are present and leave the first row unchanged.

Thanks.

Have a great day

 

[Blade Hunter]

This is untested as I just slapped it together but the logic is pretty sound. Make sure you test it on a COPY of your sheet so as to not do any damage.

Sub RemoveDupes()
Dim X As Long
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A1:A" & X), Range("A" & X).Text) > 1 Then
        Rows(X).Delete
        X = X - 1 'Normaly you would go backwards when deleting rows but because we are using an expanding range we must go forwards and therefore take 1 away from X when deleting a row
    End If
    If X > Range("A" & Rows.Count).End(xlUp).Row Then Exit For 'This is to stop it from going bealistic if the last row is a dupe
Next
End Sub

 

[Blade Hunter]

If however you want to leave the row intact but just blank out all the values, use this instead:

Sub RemoveDupes()
Dim X As Long
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range("A1:A" & X), Range("A" & X).Text) > 1 Then Rows(X).ClearContents
Next
End Sub

 

[gopinath1990]

Thanks a lot buddy u mad e my day....

 

[snsa2204]

In excel I typed 6519991852611318.37 but automatically converrted to 6519991852611310.00 last digit and decimals automatically how can it changed pls help me