• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help::Need VBA code to remove duplicate row from table.

Mandar Khedekar

New Member
Suppose I am having following data in a table ,and I have to delete the duplicate rows. In this case Book1, Book11 and Book 9 should be deleted as they are duplicate entries.


Column D Column E Column F


Book Name Number of Copies Price

Book 1 45 230

Book 2 80 450

Book 3 34 100

Book 4 22 210

Book 5 11 183

Book 6 56 234

Book 7 74 543

Book 8 46 289

Book 10 89 260

Book 11 45 230

Book 11 45 230

Book 21 80 450

Book 13 34 100

Book 1 22 210

Book 50 11 183

Book 33 56 234

Book 23 74 543

Book 81 46 289

Book 9 22 470

Book 14 89 260


I did try with the below code but didnt work


LastRow = Worksheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row


For n = 1 To LastRow


With ThisWorkbook.Sheets("Sheet1").Cells(n, 1)


If .Cells(n, 1) = .Cells(n + 1, 1) Then

rowstodelete = ThisWorkbook.Sheets("Sheet1").Cells(n, 1)

Rows(rowstodelete).Select

Selection.Delete Shift:=xlUp

End If


End With

Next n
 
Hello Mandar,


1. How come "Book 9" is a duplicate entry?

2. How will you select the row for deletion? "Book 11" has identical E & F columns but "Book 1" columns differ so which row will you be deleting? Or will you be deleting both rows.


Please explain these things.
 
Hello, Thank you for reply.

Book 9 was typo error sorry for this.

I wrote the below code and it worked fine thank you for your valuable time. Still if you have any alternate code please share.

Public Sub DeleteDuplicateRows()


Dim CompareRange As Variant, x As Variant, y As Variant


Set CompareRange = Range("D7:D16")

Range("D17:D26").Select


For Each x In Selection

For Each y In CompareRange

If x = y Then y.EntireRow.Delete

Next y

Next x


End Sub
 
Back
Top