Remove duplicate rows

[ADAMC]

Hi all,

Can someone help with me with a macro to remove duplicate rows based on an input in column E?
Heres some sample data below:

Excel Workbook
	A	B	C	D	E	F	G	H
3	Name	Db	Job Number	Addess	Exchange	Required Date	Search Results	Comments
4	blah	blah	blah	blah	MYP35222*	24/08/2011*	blah	blah
5	blah	blah	blah	blah	MYP35222*	24/08/2011*	blah	blah
6	blah	blah	blah	blah	MYP35222*	24/08/2011*	blah	blah
7	blah	blah	blah	blah	MYP35222*	24/08/2011*	blah	blah
8	blah	blah	blah	blah	MYP35222*	24/08/2011*	blah	blah
create_report 1

Basically I need to be left with only 1 row of data where duplicate code appears in column E. In the example above there is a header row which starts in row 3. Iif i run the macro on the sample above i should be left with a header row plus 1 row as the other 4 are duplicates. The start row (row 3) is always the same but the data set can change to a different end row day on day so i need the macro to run until it hits the last row.

Thanks for any help with this.

Edit** the screenshot above shows that the cells contain a * at the end of the reference number i need to remove duplicates in my actual spreadsheet there is no * not sure how this got there.

Thanks

 

[VoG]

Sorry, I missed the bit about column B

Sub NoDup()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
    If WorksheetFunction.CountIf(Range("E4:E" & i), Range("E" & i).Value) > 1 Then Rows(i).Delete
Next i
On Error Resume Next
Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
LR = Range("E" & Rows.Count).End(xlUp).Row
Columns("A").Insert
Range("A3").Value = "Cst Name"
For i = 4 To LR
    Range("A" & i).Value = Left(Range("D" & i).Value, Len(Range("D" & i).Value) - 2) & Left(Range("C" & i).Value, 2)
Next i
End Sub

 

[ADAMC]

VoG,

works brilliantly, thanks for your help

 

[ganeshpoojary05]

hi,

if you are using excel 2007 or further version than you can use the default function for removing duplicate rows. Select your data and go to Data menu and select "Remove duplicates" and then select the columns in which you want to delete the duplicate rows. kindly revert me back if its working.