MS Excel 2007 Macro to delete duplicates from another sheet....?

Question

Dear Friends...
I need an Excel macro to delete duplicates from another sheet.....let me explain.....
I am working on sheet1 and i want to run that macro from same sheet but duplicate entries should be deleted from sheet2..........
i have a macro but it doesn't work from another sheet, means if i run this macro so it will delete duplicates from active worksheet only.......but i want to delete duplicates from another worksheet not from my working sheet.......plz plz plz hellllllllllpppppppppp......:(

I am using this macro:

Sub DeleteDups_B()
Dim LastRow
LastRow = Range("B" & _
ActiveSheet.Rows.Count).End(xlUp).Row
For Each cell In Range("B2:B" & LastRow)
cell.Select
If Application.CountIf(Range("B2:B" & LastRow), _
LCase(ActiveCell.Value)) > 1 Or _
Application.CountIf(Range("B2:B" & LastRow), _
UCase(ActiveCell.Value)) > 1 Or _
Application.CountIf(Range("B2:B" & LastRow), _
Application.Proper(ActiveCell.Value)) > 1 Then
ActiveCell.EntireRow.Delete
End If
Next
End Sub

Plz help friends.....

Answer 1

As stated, you must change:



LastRow = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row



to this, for example:



LastRow = Sheets("Sheet2").Range("B" & .Rows.Count).End(xlUp).Row



But, more importantly, when deleting rows in a macro you must begin at the last row and evaluate to the first row. Otherwise, Excel gets confused and the macro does not function completely appropriately.



So, you would need to do a 'For' loop:



For i = LastRow to 2 Step -1



your code



Next i

Answer 2

Highlight all data, then up to Data, Sort and sort by whatever field you want, i.e. surname. All duplicates will therefore be together and you can easily delete them. However, presumably as you have so many addresses you are using Excel as a database but when you get to this size, then you really need to export to Access where you can create queries and reports etc much more easily.

Answer 3

http://stackoverflow.com/questions/3419573/excel-vba-remove-duplicate-rows-by-cross-referencing-2-different-sheets-then-d



Your code isn't naming/assigning Worksheets first - must do that.