Remove duplicate rows

[richiwatts]

I have 3000 rows in my database and 2000 of them are now duplicate rows. how can i get rid of the duplicate rows?

 

[Jim North]

DATA

FILTER

ADVANCED FILTER

leave CRITERIA blank

check the COPY TO A NEW LOCATION

check the UNIQUE RECORDS ONLY

 

[richiwatts]

Still having a bit of a problem

When I use the advanced filter it gets rid of the duplcate rows, but, some of the rows use more columns than others. After I have pasted the data from the advanced filter it only pastes the first two columns. Some of the rows use up to 5 columns. Why is it doing this and how can i get round it?

 

[WJReid]

Hi Richiwatts,

Copy the headings from the original columns ot the new location then do the advanced filter. If you have copied the headings to say H1:L1, then in the copy to range of the advanced filter dialogue box type H1:L1. this should copy all of the columns across.

Regards,

Bill

 

[richiwatts]

This is really driving me crazy.

I probably could have done it manually with all the time I have spent trying to do it.

Anyway, this is what I have in the advanced filter.

list range :
sheet1!$A$1:$G$3288
sheet1!$A$3289:$G$3289

When it doesn't crash it removes the duplicates but still only gives me the first two columns. What am I doing wrong. I am getting despirate!!

 

[Jim North]

Rich,

Have you got this working yet?

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-09-04 07:15

 

[richiwatts]

i did it manually in the end and it took for ever.

Thanks anyway for the help you gave

Richi

 

[shades]

This will do the trick, if the column you want is the farthest left and is sorted.

Sub delete_duplicates()
rowx = 1
Do Until Cells(rowx + 1, 1).Value = ""
If UCase(Cells(rowx, 1).Value) = UCase(Cells(rowx + 1, 1).Value) Then
Cells(rowx + 1, 1).EntireRow.Delete
Else
rowx = rowx + 1
End If
Loop
End Sub

Takes a little while. But I did one with 16,000 rows last Friday - and it took about 4 minutes to run. Far better than by hand, though.